• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unmerge bunch of 10 digit numbers in excel

rshanka2

New Member
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
 
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)&"]"),"")
 
With Power Query here is an alternative means to get what you want

l
Code:
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.
 
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:
71990
 
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)

71997
Data is the cell A2 and k is the range B1:B7 containing the digits 1-6 with number formatting to add text.
 
Last edited:
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'.
 
Back
Top