Split data between phone number and next name

[Leeta77]

I have some inconsistent phone number data which i need to split into separate columns. I cannot work out how to do it.

Any ideas would be appreciated.

[Gregb11]

in the file you attached, are columns B and C what you want to see? So you want the name and the phone number in one cell? can you add what you expect to see for the other rows too (rows 2:5)?

[Leeta77]

Hi Greg, Yes column B and C are what I want to see.
The name and phone number can be in one or 2 cells but they need to be in the right order. I have tried the split number and character feature but it put all names in one column and all numbers in another.
I will update the other rows as requested.
Cheers

[bebo021999]

In B2:
=IFERROR(LEFT(MID(SUBSTITUTE($A2," ",""),SEARCH(A9,SUBSTITUTE($A2," ",""))+LEN(A9),255),AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,MID(SUBSTITUTE($A2," ",""),SEARCH(A9,SUBSTITUTE($A2," ",""))+LEN(A9),255)),1)+9),"")

C2:
=IF(B2="","",IFERROR(LEFT(MID(SUBSTITUTE($A2," ",""),SEARCH(B2,SUBSTITUTE($A2," ",""))+LEN(B2),255),AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,MID(SUBSTITUTE($A2," ",""),SEARCH(B2,SUBSTITUTE($A2," ",""))+LEN(B2),255)),1)+9),""))
drag C2 accross

Drag all down

[Gregb11]

******EDIT - I did not see Bebo's response before I posted this. Looks like his formulas are much more efficient, as I figured they would be ***********

I have no doubt that these formulas can be refined, but it's late and they work (as far as I can tell), but maybe you (or someone else here) can make them a little more efficient.

In B2, enter this formula:

Please Login or Register  to view this content.

Then you can copy this down the column.

In C2, enter this formula:

Please Login or Register  to view this content.

Copy this down and then copy it across.

See attached.

[kvsrinivasamurthy]

Here is UDF code

Please Login or Register  to view this content.

UDF

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List

[Leeta77]

Hi,

Thanks for your reply

I like the look of your result but i can't get it to work. How and where do i apply the function? I've added it in VB and it's showing on the list.

Ive tried adding in to B2 and selecting A2 but no good. What do i enter when it ask for Ip and K? I've tried lots of options with no luck.
Thanks!

[kvsrinivasamurthy]

File should be saved as .xlsm (Macro enabled file).
Pl see the file. Formula is in B2 then copied to other region.
In Formula in B2
A2 is the text and Columns function shows the number of the portion to be selected.
I think it is clear. Any further clarifications welcome.

[Leeta77]

Wonderful! Thank you, it's working now

[Leeta77]

Thanks Greg!

[Leeta77]

Thanks Bebo!

[kvsrinivasamurthy]

Welcome. Thanks for feed back.