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:
Sort by date Sort by votes

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
ok lets try these

B1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
C1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
D1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
E1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))&TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-1)*LEN($A1)+1,LEN($A1)))
F1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-1)*LEN($A1)+0,LEN($A1))) &" "&TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-0)*LEN($A1)+0,LEN($A1)))
G1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-0)*LEN($A1)+1,LEN($A1)))
H1
Code: 
=LEFT(LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+7,100),100),FIND("""",LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+4,100),100),1)-4)
I1
Code: 
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+LEN(H1)+6))

No promises this time Lol
 
Upvote 0
AkaTrouble said:
ok lets try these

B1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
C1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
D1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))
E1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1)))&TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-1)*LEN($A1)+1,LEN($A1)))
F1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-1)*LEN($A1)+0,LEN($A1))) &" "&TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-0)*LEN($A1)+0,LEN($A1)))
G1
Code: 
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMN()-0)*LEN($A1)+1,LEN($A1)))
H1
Code: 
=LEFT(LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+7,100),100),FIND("""",LEFT(MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+4,100),100),1)-4)
I1
Code: 
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+LEN(H1)+6))

No promises this time Lol
Click to expand...

Your really genius.. You have done a magic here and made my work so easy...

Simply superb. I dont have words to express my joy..

Thank you so much:)
 
Upvote 0
Pranuvins,

See if the following screenshots will help you?


Excel 2007
A
1123456 James Vasanth (00) 123-456 (00) 789-101 [email]JamesBvasanth@mail.com[/email] Tech lead "NO 01, 23rd street, Pune"
Sheet1



Excel 2007
B
1123456
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND(" ",A1,1))



Excel 2007
C
1James
Sheet1
Cell Formulas
RangeFormula
C1=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))



Excel 2007
D
1Vasanth
Sheet1
Cell Formulas
RangeFormula
D1=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)



Excel 2007
E
1(00) 123-456 (00) 789-101
Sheet1
Cell Formulas
RangeFormula
E1=MID(A1,FIND("(",A1,1),25)



Excel 2007
F
1JamesBvasanth@mail.com
Sheet1
Cell Formulas
RangeFormula
F1=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))



Excel 2007
G
1Tech lead
Sheet1
Cell Formulas
RangeFormula
G1=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)



Excel 2007
H
1"NO 01, 23rd street, Pune"
Sheet1
Cell Formulas
RangeFormula
H1=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+3))
 
Upvote 0
hiker95 said:
Pranuvins,

See if the following screenshots will help you?

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

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Excel 2007
B
1123456

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B1=LEFT(A1,FIND(" ",A1,1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
C
1James

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
C1=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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
D
1Vasanth

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
D1=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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
E
1(00) 123-456 (00) 789-101

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
E1=MID(A1,FIND("(",A1,1),25)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
F
1JamesBvasanth@mail.com

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
F1=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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
G
1Tech lead

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
G1=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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Excel 2007
H
1"NO 01, 23rd street, Pune"

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
H1=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+LEN(F1)+LEN(G1)+3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
Hi Hiker95

Thanks for your time in helping me.
 
Upvote 0
If you are up for a VBA macro solution, this should work for you...
Code: 
Sub SplitData()
  Dim R As Long, C As Long, Data As Variant, Result As Variant
  Dim Txt() As String, Parts() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 8)
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), ") ", ")")
    Parts = Split(Data(R, 1), , 7)
    For C = 1 To 6
      Result(R, C) = Replace(Parts(C - 1), ")", ") ")
    Next
    Txt = Split(Parts(6), " """)
    Result(R, 7) = Txt(0)
    Result(R, 8) = """" & Txt(1)
  Next
  Range("A10").Resize(UBound(Result), 8) = Result
End Sub
 
Upvote 0
Try this formula although I can't guarantee that it will work in every case
Enter in B1 and drag formula across until you blanks.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ("," |("),"""","|")," ","|",1)," ","|",1)," ","|",5)," ","|",5),"|",REPT(" ",255)),255*(COLUMNS($A:A)-1)+1,255))
 
Upvote 0
Rick Rothstein said:
If you are up for a VBA macro solution,
Click to expand...

i have put some comments in the code along with some ??? I couldn't understand the logic of process sorry

Code: 
Sub SplitData()
  Dim R As Long, C As Long, Data As Variant, Result As Variant
  'standard declarations
  
  Dim Txt() As String, Parts() As String
  'array declarations ??
  
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  'find last used row in A:A
  
  ReDim Result(1 To UBound(Data), 1 To 8)
  'number of times to split data ??
  
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), ") ", ")")
    'current row replace extra space if there with one space after ) ??
    
    Parts = Split(Data(R, 1), , 7)
    'actual split of data ??
    
    For C = 1 To 6
      Result(R, C) = Replace(Parts(C - 1), ")", ") ")
    Next
    
    'replace extra space if it existed ??
    
    
    Txt = Split(Parts(6), " """)
    ' ???
    Result(R, 7) = Txt(0)
    ' ???
    Result(R, 8) = """" & Txt(1)
    ' ???
  Next
  Range("A10").Resize(UBound(Result), 8) = Result
  'return results back to worksheet starting from A10
  
End Sub
 
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
837
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
734
tjdickinson
T
D
  • Question
Macro to loop through one workbook, search another book, output info from 2nd book into 1st
Replies
2
Views
602
DrParmeJohnson
D

Forum statistics

Threads
1,215,480
Messages
6,125,050
Members
449,206
Latest member
Healthydogs

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