+ Reply to Thread
Results 1 to 26 of 26

Convert multiple serial number text in column into a single column of one SN only

  1. 06-01-2023, 01:15 PM #1
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Convert multiple serial number text in column into a single column of one SN only

    Have column A SHEET 1 which is 50 rows. Column A is populated with numeric SNs of 6 to 8 digits in length (none have leading zeros and are text formatted.) The cells may have from 1 to 20 serial numbers in each cell, separated by one space. There are no duplicate SNs. I would like to get all the serial numbers put into a single column in SHEET 2 column B with only one SN in each cell.

    Example of contents of two cells from column A:

    Cell A1: 32229236 38227217
    Cell A2: 6257290 6417295 6219291 6217293 6217288 6217300

    Warning - I'm not a programmer.

    Thanks for any guidance on a solution.
    Reply With QuoteRegister To Reply
  2. 06-01-2023, 01:32 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    Try:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copied across and down.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-02-2023, 05:46 AM #3
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,005

    Re: Convert multiple serial number text in column into a single column of one SN only

    If you want to merge into one column, you can use a helper column to solve it.

    B1=1

    B2
    Please Login or Register  to view this content.
    copied down.

    C1
    Please Login or Register  to view this content.
    copied down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-02-2023, 09:33 PM #4
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Rather remarkable TMS its working to get the text broken into their own individual column.

    Windknife have not figured that out yet, does that result in one single column?

    So impressed with you geniuses - THANKYOU !!

    J
    Reply With QuoteRegister To Reply
  5. 06-02-2023, 10:00 PM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  6. 06-02-2023, 10:23 PM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Convert multiple serial number text in column into a single column of one SN only

    with Power Query

    raw
    32229236
    38227217
    6257290
    6417295
    6219291
    6217293
    6217288
    6217300 


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 06-04-2023, 03:33 PM #7
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Thanks all for the inputs, the impressive formula to converted the combined serial number text into their own individual cells worked. Strange there were a few instances that it do not convert perfectly, but cleaned them all up. Never figured out why, guessing had something to do with the data entry. Now have 50 rows with one serial number in their own cell. The number of cells populated in each row varies from 1 to 22 (i.e. column A to V). I can't figure out how to implement the provided solutions to get this data into one column. Thanks for your patience.

    J
    Reply With QuoteRegister To Reply
  8. 06-04-2023, 03:38 PM #8
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Convert multiple serial number text in column into a single column of one SN only

    excel example file?
    Reply With QuoteRegister To Reply
  9. 06-04-2023, 05:18 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Convert multiple serial number text in column into a single column of one SN only

    Also if you have upgraded from Excel 2010 functions in Office 365 can make short work of this.
    Dave
    Reply With QuoteRegister To Reply
  10. 06-04-2023, 05:20 PM #10
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Challenge is convert these text numbers which are in their own individual cells in one long column of data.


    row 1 6217290 6217295 6217291 6217293 6217288 6217300
    row 2 6217289 6217297 6217304 6217299 6217292 6217301 6217303 6217302
    row 3 6217296 6217294
    row 4 6217298
    row 5 11211656 11211657 11211658 11211659
    Attached Files Attached Files
    Last edited by JET2011; 06-04-2023 at 05:25 PM.
    Reply With QuoteRegister To Reply
  11. 06-04-2023, 05:23 PM #11
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Convert multiple serial number text in column into a single column of one SN only

    any expected result?
    Reply With QuoteRegister To Reply
  12. 06-04-2023, 05:26 PM #12
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    If you DO have 365, one way:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 06-04-2023, 05:26 PM #13
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Expected result is place all the serial numbers in one continuous column.
    Reply With QuoteRegister To Reply
  14. 06-04-2023, 05:28 PM #14
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Convert multiple serial number text in column into a single column of one SN only

    sure so XL2010 with Power Query add-in

    Please Login or Register  to view this content.
    Value
    6217290
    6217295
    6217291
    6217293
    6217288
    6217300
    6217289
    6217297
    6217304
    6217299
    6217292
    6217301
    6217303
    6217302
    6217296
    6217294
    6217298
    11211656
    11211657
    11211658
    11211659
    13213568
    13213562
    13213565
    13213569
    13213570
    13213563
    13213571
    13213567
    13213566
    13213572
    13213573
    19218105
    19218104
    19218106
    19218100
    19218102
    19218103
    19218101
    19218099
    31219394
    31219395
    31219393
    31219396
    31219392
    31219397
    31219400
    31219398
    31219399
    34212408
    34212406
    34212403
    34212432
    34212443
    34212405
    34212424
    34212429
    34212414
    34212412
    34212456
    34212459
    34212457
    34212458
    34212439
    34212438
    34212421
    34212411
    34212435
    34212446
    34212415
    34212478
    34212426
    34212420
    34212452
    34212401
    34212402
    34212423
    34212425
    34212428
    34212422
    34212462
    34212491
    34212445
    34212444
    34212448
    34212449
    34212479
    34212440
    34212436
    34212480
    34212487
    34212461
    Attached Files Attached Files
    Last edited by sandy666; 06-04-2023 at 05:38 PM.
    Reply With QuoteRegister To Reply
  15. 06-04-2023, 05:31 PM #15
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    With your sample file:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 06-04-2023, 05:40 PM #16
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Do not have 365, I think.
    Reply With QuoteRegister To Reply
  17. 06-04-2023, 05:52 PM #17
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    That's a shame. Guess it's PQ then.
    Reply With QuoteRegister To Reply
  18. 06-04-2023, 07:12 PM #18
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    I do have Excel for MS 365. Still not figured it out. Tried both of the transpose formulas. Able to get the formula to only replicate the data in column 1. How I used the formula is past it in cell w1, then it immediately takes data A1-A20 from column 1 and populated in cell W1 to W20.
    Last edited by JET2011; 06-04-2023 at 07:16 PM.
    Reply With QuoteRegister To Reply
  19. 06-04-2023, 07:33 PM #19
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Convert multiple serial number text in column into a single column of one SN only

    first update your profile to 365
    second: power query should work without any problem
    Reply With QuoteRegister To Reply
  20. 06-04-2023, 07:38 PM #20
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    The first formula takes multiple serial numbers in cells in column A … like your original example.

    The second formula takes multiple serial numbers in separate cells, that is, one per cell in rows 1 to 20 … like your second example.

    See the attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  21. 06-04-2023, 08:40 PM #21
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Convert multiple serial number text in column into a single column of one SN only

    Finally figured it out using the two formulas! Have not figured out the PQ yet, will work on it more this week. (will marked solved later)

    This is great to know how this is done.

    Thank you all!

    J
    Reply With QuoteRegister To Reply
  22. 06-04-2023, 09:09 PM #22
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Convert multiple serial number text in column into a single column of one SN only

    with PQ maybe it will help a bit
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  23. 06-04-2023, 09:15 PM #23
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Convert multiple serial number text in column into a single column of one SN only

    Similar to Trevors's (TMS) without the TRANSPOSE.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by FlameRetired; 06-04-2023 at 09:30 PM.
    Reply With QuoteRegister To Reply
  24. 06-04-2023, 10:24 PM #24
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Convert multiple serial number text in column into a single column of one SN only

    @JET2011: You're welcome. Thanks for the rep.


    @FR: that's annoying, I know that and I’ve used it before in similar situations.
    Reply With QuoteRegister To Reply
  25. 06-04-2023, 10:55 PM #25
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,433

    Re: Convert multiple serial number text in column into a single column of one SN only

    Another solution:

    Please try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by HansDouwe; 06-04-2023 at 11:15 PM.
    Reply With QuoteRegister To Reply
  26. 06-05-2023, 06:48 PM #26
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Convert multiple serial number text in column into a single column of one SN only

    Quote Originally Posted by TMS View Post
    @FR: that's annoying, I know that and I’ve used it before in similar situations.
    Hey been there, done that. LOL Actually I suspected you did.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert values in multiple rows to single column
    By udupashashank in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-16-2022, 04:13 AM
  2. Help combining text column from multiple worksheets into single column, single worksheet?
    By mblanco59 in forum Excel General
    Replies: 1
    Last Post: 01-29-2020, 05:19 PM
  3. i need to convert multiple rows into single column
    By mmans786 in forum Excel General
    Replies: 3
    Last Post: 08-08-2015, 12:02 PM
  4. [SOLVED] Count text in one column Matching single or multiple Criteria from Other Column Excel 2003
    By Jose Macieira in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 06:55 AM
  5. Convert multiple rows to one single column
    By Rashme in forum Excel General
    Replies: 2
    Last Post: 09-12-2012, 03:20 PM
  6. Convert Serial Number to Date WITH TEXT
    By HP RodNuclear in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2010, 12:39 PM
  7. convert single column to multiple columns
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-07-2007, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1