Unmerge bunch of 10 digit numbers in excel

[rshanka2]

Hi

I have a bunch of 10 digit numbers in excel. Below is an example
How do I split them so that all the individual 10 digit number are accounted for (6100238281, 6100238141, etc)
When i do lookups it only grabs the first number. I tried text to columns but doesn't work.
Appreciate any help.

Thank You

CELL A 6100238281 6100238141 6100238246 6100238142 6100238126

 

[bosco_yip]

Try,

In B2 formula copied across right :

=TRIM(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",99)),COLUMN(A1)*99-98,99))

 

[shrivallabha]

Another formula approach using FILTERXML can be

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A2,CHAR(10),"</s><s>")&"</s></t>","//s["&COLUMNS($A$1:A$1)&"]"),"")

 

[AlanSidman]

With Power Query here is an alternative means to get what you want

l

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"})
in
    #"Split Column by Delimiter"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[p45cal]

I tried text to columns but doesn't work

I managed text to columns; choose Other in step 2, make sure there's nothing in the associated box, then with the text cursor still in that box, on the keyboard, hold down the left Alt key, then on the numeric keypad (NOT the number keys across the top of the keyboard) type 010, then let go of the Alt key (you won't see anything obvious in the box). The result is:

 

[Peter Bartholomew]

Because the OP states that the numbers have exactly 10 digits, one can simply split on field width:
= MID(data, 1+11*(k-1), 10)


Data is the cell A2 and k is the range B1:B7 containing the digits 1-6 with number formatting to add text.

 

[shrivallabha]

I managed text to columns; choose Other in step 2, make sure there's nothing in the associated box, then with the text cursor still in that box, on the keyboard, hold down the left Alt key, then on the numeric keypad (NOT the number keys across the top of the keyboard) type 010, then let go of the Alt key (you won't see anything obvious in the box). The result is:
View attachment 71990

The other route is to key in 'CTRL+J'.