Formula to split words from a cell

[Pranesh]

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:

A1	B1	C1	D1	E1	F1	G1	H1
123456 <tbody> </tbody>	James	Vasanth	(00) 123-456	(00) 789-101	JamesBvasantha@mail.com	Tech 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!!

 

[Rick Rothstein]

If you are up for a VBA macro solution, this should work for you...

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("[B][COLOR="#FF0000"]B1[/COLOR][/B]").Resize(UBound(Result), 8) = Result
End Sub

With the data assumed to be starting in cell A1, I inadvertently posted my code originally with my test output starting cell as A10 rather than the starting cell the OP wanted (B1). Use the corrected code above instead of the code I posted in Message #18 (assuming you decide to use a macro instead of formulas for this task).

 

[AkaTrouble]

With the data assumed to be starting in cell A1, I inadvertently posted my code originally with my test output starting cell as A10 rather than the starting cell the OP wanted (B1). Use the corrected code above instead of the code I posted in Message #18 (assuming you decide to use a macro instead of formulas for this task).

hope my last comment was not read as correcting your macro with regards to where you placed result back on page...

my comments in my pasted copy of the code were more questions than answers

 

[Rick Rothstein]

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

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 ??
[COLOR="#FF0000"]Yes, dynamic array declarations... more efficient than Variant arrays so I use them when possible[/COLOR]  

  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  'find last used row in A:A
[COLOR="#FF0000"]Actually, it assigns all the values in Column A, from Row 1 to the last row of data in Column A, to the Variant variable... doing this stores the assigned values as a two-dimensional array (even though only one column is involved)[/COLOR] 

  ReDim Result(1 To UBound(Data), 1 To 8)
  'number of times to split data ??
[COLOR="#FF0000"]We know there will be 8 columns of data outputted, so I create an array having as many rows as there is data and 8 columns in order to have a place to put the output values while they are being calculated[/COLOR]
  
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), ") ", ")")
    'current row replace extra space if there with one space after ) ??
[COLOR="#FF0000"]Since the phone numbers have a space in them (after the closing parenthesis), and since my next line of code will split the data based on space delimiters, I needed to hide that particular space from the Split function, so I removed it with the intention of putting it back later[/COLOR] 

    Parts = Split(Data(R, 1), , 7)
    'actual split of data ??
[COLOR="#FF0000"]Yes, but only into 7 pieces... this way, all the remaining text after the first 6 spaces will be placed in the last array element even if that text contains spaces. I needed to do this because what follows the email address and precedes the quote mark can be made up of one or more space delimited words which must be kept together, so I decided to handle the last two columns of output later on[/COLOR]
    
    For C = 1 To 6
      Result(R, C) = Replace(Parts(C - 1), ")", ") ")
    Next
    'replace extra space if it existed ??
[COLOR="#FF0000"]I am just putting back the space that I removed from the phone number earlier[/COLOR]
    
    Txt = Split(Parts(6), " """)
    ' ???
[COLOR="#FF0000"]Remember I said earlier that I put off handling all the text that ended up in the last array element... well is is where I do that handling. First I split the text in that last element using the space/quote as the delimiter[/COLOR]

    Result(R, 7) = Txt(0)
    ' ???
[COLOR="#FF0000"]then I put the first element of that split into the next output array element[/COLOR]

    Result(R, 8) = """" & Txt(1)
    ' ???
[COLOR="#FF0000"]and then put the second element of that split into the last array element[/COLOR]

  Next
  Range("A10").Resize(UBound(Result), 8) = Result
  'return results back to worksheet starting from A10
[COLOR="#FF0000"]I did not mean to put the output in Column A below the existing data... I did that during while developing the macro code so I could see the long original text string and the parts my code split them into. I fully meant to change the output to cell B1, but forgot to do so before posting the code. I corrected this in my follow up posting in Message #21.[/COLOR]
  
End Sub

See above comments in red.

 

[Rick Rothstein]

If you are up for a VBA macro solution, this should work for you...

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("B1").Resize(UBound(Result), 8) = Result
End Sub

If you would rather not use the above macro, I have a formula solution (different from what has been posted so far) which you can use...

Put this formula in cell B1...

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE($A1,") ",")")," ",REPT(" ",300)),COLUMNS($B:B)*300,300)),")",") ")

and copy it across to cell G1, then put these two formulas in the indicated cells...

H1: =MID(LEFT($A1,FIND(" """,$A1)-1),FIND($G1,$A1)+LEN($G1)+1,99)

I1: =""""&TRIM(RIGHT(SUBSTITUTE(A1," """,REPT(" ",200)),200))

then copy the range B1:I1 down to the end of your data.

 

[deep15thakur]

If you have data in cell A1 then try this
Paste this formula to A3 cell & drag it to H3 cell where your data ends.

TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",LEN($A$1))),(COLUMN()-1)*LEN($A$1)+1,LEN($A$1)))

 

[Rick Rothstein]

If you have data in cell A1 then try this
Paste this formula to A3 cell & drag it to H3 cell where your data ends.

TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",LEN($A$1))),(COLUMN()-1)*LEN($A$1)+1,LEN($A$1)))

Did you actually try that formula against the examples the OP posted? When I try it, I find it does not work.
.

 

[AkaTrouble]

See above comments in red.

Thank You Very much . this has helped loads, having understood the need and a logic to provide my earlier long winded solutions it was good to understand how you applied this logic to a working code.

think i am still a long way from being able to do the code bit but it does help lots understanding the logic of the problem to a coding method.


still lots to learn but willingness of Great posters to reply to side questions is helping lots. Although still not always the best solutions i am managing to offer more and more working ones to other members. and learning lots in the process.

 

[Rick Rothstein]

Thank You Very much . this has helped loads, having understood the need and a logic to provide my earlier long winded solutions it was good to understand how you applied this logic to a working code.

You are quite welcome... I am glad what I posted was helpful to you.

think i am still a long way from being able to do the code bit but it does help lots understanding the logic of the problem to a coding method.

I don't want this to sound discouraging, but I have been coding (first in various forms of BASIC, then Visual Basic, then VBA along with some other languages along the way) since 1981, so a lot of my coding style is automatic and was "locked in" years and years ago. My advice to you is to keep coding... it becomes easier the more you do it.

still lots to learn but willingness of Great posters to reply to side questions is helping lots. Although still not always the best solutions i am managing to offer more and more working ones to other members. and learning lots in the process.

Again, you are welcome. And please do keep posting your solutions to questions asked here.

 

[Pranesh]

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

Hi AKA trouble & Rick Rothstein,

sorry to trouble you again.

Now my scenario has changed little bit. can you please help me with this too?

A1 - Input

19191 James Anderson  jamesHanderson@mail.com 508-985-4849 female 76 "Wednesday, January 06,  1937" 396513 Jam1930 uL4ae 4961 dane street ACUSHNET Massach United  States 2743 ABC furnitures "Wednesday, February 22, 2012" Library  assistant 2002 Ford Tonka ACTIVE

Output which I need is showN below

B1

C1

D1

E1

F1

G1

H1

I1

J1

K1

L1

M1

N1

O1

P1

Q1

R1

S1

T1

U1

V1

1919

James

Anderson

jamesHanderson@mail.com

508-985-4849

Female

76

"Wednesday, January 06, 1937"

396513

Jam1930

uL4aE

4961 Dane street

ACUSHNET

Massach

Unites States

2743

ABC Furnitures

"Wednesday, February 22, 2012"

Library Assistant

2002 Ford Tonka

ACTIVE

<tbody>
</tbody>

 

[Pranesh]

Can anyone help me with this?