Formula to split words from a cell

P

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
219
Hi,

I have a set of associates data which are placed in excel under single cell. I need that data to be split into 8 cells.

Please find below example.

My data

A1
123456 James Vasanth (00) 123-456 (00) 789-101 JamesBvasanth@mail.com Tech lead "NO 01, 23rd street, Pune"

<tbody>
</tbody>

Output:

A1B1C1D1E1F1G1H1
123456

<tbody>
</tbody>
JamesVasanth(00) 123-456(00) 789-101JamesBvasantha@mail.comTech lead"NO 01, 23rd street, Pune"

<tbody>
</tbody>
So the entire data in cell A1 should be split in to 8 cells as shown above.

The logic which i have is at the start always it will be numbers that should be split in A1.
followed by that we have Name that should be split into B1 & C1 which is first & last name.
Then we have 2 contact numbers which always start with open bracket ( which should be split into D1 & E1
Then email ID need to be split in F1
Followed by that designation in G1
Then atlast in H1 we will have address and always it starts with " and ends with "

Can anyone help me with formula or VBA which will be much helpful. Thanks!!
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
There is no consistent delimiter in the data. Excel has a Text to Columns tool that allows you to break up cells like this into multiple columns based on some set width or character (like a space or comma). See if you can have this data exported as a ".csv" file, then you can use Text to Columns in Excel and choose a comma as your Delimiter.
 
Upvote 0
akicker said:
There is no consistent delimiter in the data. Excel has a Text to Columns tool that allows you to break up cells like this into multiple columns based on some set width or character (like a space or comma). See if you can have this data exported as a ".csv" file, then you can use Text to Columns in Excel and choose a comma as your Delimiter.
Click to expand...

Thank you for your reply

I tried it but as data is not even i was unable to do delimiter to split this. Any FIND MID SEARCH formulas will work?
 
Upvote 0
these are not the tidiest formula but based on your single example seems to achieve the desired results

based on your example in cell A1

Code: 
[TABLE="width: 693"]
<colgroup><col width="693"></colgroup><tbody>[TR]
   [TD="width: 693"]123456 James   Vasanth (00) 123-456 (00) 789-101 JamesBvasanth@mail.com Tech lead "NO   01, 23rd street, Pune"[/TD]
 [/TR]
</tbody>[/TABLE]

in B1
Code: 
=LEFT(A1,FIND(" ",A1,1))
in C1
Code: 
=LEFT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1))
in D1
Code: 
=RIGHT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1)+1)
in E1
Code: 
=MID(A1,FIND("(",A1,1),25)
in F1
Code: 
=LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+3,100),FIND(" ",MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+3,100),1))
in G1
Code: 
=LEFT(LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+3,100),100),FIND("""",LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+3,100),100),1)-2)
in H1
Code: 
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+3))

i cant vouch for reliabilty based on one example
 
Upvote 0
to split the number cell into 2 cells

insert new column making E1 and F1 for numbers

in E1

Code: 
=MID(A1,FIND("(",A1,1),12)

in (new) F1

Code: 
=RIGHT(MID(A1,FIND("(",A1,1),25),12)
 
Upvote 0
AkaTrouble said:
to split the number cell into 2 cells

insert new column making E1 and F1 for numbers

in E1

Code: 
=MID(A1,FIND("(",A1,1),12)

in (new) F1

Code: 
=RIGHT(MID(A1,FIND("(",A1,1),25),12)
Click to expand...

Thanks AKA for your help..

I tried your formulas B1 & H1 works perfect other than that no luck:(
 
Last edited:
Upvote 0
we would need to change the other formulas because of change

so in A1 leave then B1 onwards try these

B1
Code: 
=LEFT(A1,FIND(" ",A1,1))
C1
Code: 
=LEFT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1))
D1
Code: 
=RIGHT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1)+1)
E1
Code: 
=MID(A1,FIND("(",A1,1),12)
F1
Code: 
=RIGHT(MID(A1,FIND("(",A1,1),25),12)
G1
Code: 
=LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+4,100),FIND(" ",MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+4,100)))
H1
Code: 
=LEFT(LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(G1)+LEN(F1)+4,100),100),FIND("""",LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(G1)+LEN(F1)+4,100),100),1)-2)
I1
Code: 
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+LEN(H1)+4))

not i have removed "Address" quotes from Address
 
Upvote 0
If you have data in cell A1 then try this
TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",LEN($A$1))),(COLUMN()-4)*LEN($A$1)+1,LEN($A$1))) & paste this to B1-H1.
 
Upvote 0
AkaTrouble said:
we would need to change the other formulas because of change

so in A1 leave then B1 onwards try these

B1
Code: 
=LEFT(A1,FIND(" ",A1,1))
C1
Code: 
=LEFT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1))
D1
Code: 
=RIGHT(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),FIND(" ",MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)),1)+1)
E1
Code: 
=MID(A1,FIND("(",A1,1),12)
F1
Code: 
=RIGHT(MID(A1,FIND("(",A1,1),25),12)
G1
Code: 
=LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+4,100),FIND(" ",MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+4,100)))
H1
Code: 
=LEFT(LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(G1)+LEN(F1)+4,100),100),FIND("""",LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(G1)+LEN(F1)+4,100),100),1)-2)
I1
Code: 
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+LEN(H1)+4))

not i have removed "Address" quotes from Address
Click to expand...

This is what my output is"

B1C1D1E1F1G1H1I1
123456

<tbody>
</tbody>
James

<tbody>
</tbody>
Vasan

<tbody>
</tbody>
(00) 123-456

<tbody>
</tbody>
(00) 789-101

<tbody>
</tbody>
1

<tbody>
</tbody>
JamesBvasanth@mail.com Tech lead

<tbody>
</tbody>
"NO 01 23rd street Pune"

<tbody>
</tbody>

<tbody>
</tbody>


I tried with few other examples and as said earlier B1 & I1 formula is working fine..

Thanks for your help. Is there a possibilities to figure it out.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Splitting text into columns where text is located across multiple adjacent cells?
Replies
7
Views
812
Peter_SSs
Peter_SSs
Oakie_Uffda
  • Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
H
  • Question
How to create multiple worksheets from a list of cell values based on PO number
Replies
4
Views
3K
hansgrandia
H
T
  • Question
Counting cells in a column in an array based on criteria
Replies
0
Views
720
tjdickinson
T
D
  • Question
Macro to loop through one workbook, search another book, output info from 2nd book into 1st
Replies
2
Views
591
DrParmeJohnson
D

Forum statistics

Threads
1,214,875
Messages
6,122,044
Members
449,063
Latest member
ak94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top