Extracting data from a string to a new cell

[dougsworth]

I have a single column of data in the format:

<subject>, <user name>, <date>, <time>

What i'd like is to be able to separate these so that the values are displayed in single cells.

Eg, A1: <subject>, <user name>, <date>, <time>
b2: <subject>
c3: <user name>
d4: <date>
e5: <time>

I can get the <subject> to display in B2 correctly by using the following formula in B2

=LEFT(A2,IF(ISERROR(FIND(",",A1,1)),LEN(A1),FIND(",",A1,1)-1)

But can't get it to work for the other data i want

Anyone ideas as how to do this? Any help much appreciated

Jason

 

[LSTAN]

Hi

Try using this custom function;

Function extractString(txt, n, Seperator) As String
Dim myTxt As String, TempElement As String
Dim ElementCount As Integer, i As Integer
myTxt = txt
'remove excess spaces if your separator is a SPACE
If Seperator = Chr(32) Then myTxt = Application.Trim(myTxt)

'add a separator at the end, if required
If Right(myTxt, 1) <> Seperator Then myTxt = myTxt & Seperator

ElementCount = 0
TempElement = &quot;&quot;

For i = 1 To Len(myTxt)
If Mid(myTxt, i, 1) = Seperator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
extractString = TempElement
Exit Function
Else
TempElement = &quot;&quot;
End If
Else
TempElement = TempElement & Mid(myTxt, i, 1)
End If
Next i
extractString = &quot;&quot;
End Function


Take for example; the contents in cell A1 is:
Test Range,LSTAN,12/04/2002,12.03am
[tt]
=extractString($A$1,1,&quot;,&quot will return &quot;Test Range&quot;[/tt]

and
[tt]
=extractString($A$1,3,&quot;,&quot will return &quot;12/04/2002&quot;[/tt]

Hope this helps.

rgrds
LSTAN

 

[AngO]

Dougsworth,
Maybe a strange solution, but you can also use Word to help if the file is not huge.

Copy column A to a word document.

It will probably appear as a table. If so, select the table. Then, Table, Convert Table to text. Seperated with commas.

Select all the text again. Table, Convert Text to Table, choose Comma seperator. The information is now all in seperate cells. It can be copied back to Excel. It will be across, but you can always transpose it.

Good Luck!
AngO

 

[xlbo]

Coupla solutions - copy your formula into col B so you have identical sets of data.
Then highlight column B and goto Data > Text To Columns, choose delimited, choose comma, bish bosh wollop, there ya go

For the Username field, this'll find it:
=MID(A1,FIND(&quot;,&quot;,A1)+1,FIND(&quot;,&quot;,A1,FIND(&quot;,&quot;,A1)+1)-FIND(&quot;,&quot;,A1)-1)

But your other 2 fields are gonna be horrors - I would use the code that's been posted or use the text to columns method
HTH
Geoff

 

[dougsworth]

Great! That's sorted that out! Thanks for your time and speedy responses! - Jason

 

[xlbo]

Taking on Dale's role for a milisecond - you might want to award a star to whichever solution was the most useful - as he has said many times, it's the only recognition there is here and I think people do appreciate it.
Dale - hope I'm not stepping on your toes here ;-)
Geoff

 

[AlaskanDad]

This one's been eating away at me...

Here is a non-VB solution:

Cell A1 has your needed data. Cell B1 has this formula:

=LEFT($A1,FIND(&quot;,&quot;,$A1,1)-1) This just gives you the first one but it sets the seed for all the others.

Cell C1 (then dragged to the right for as many as you need) has this formula:

=IF(ISERROR(TRIM(MID($A1 & &quot;,&quot; & &quot;,&quot;,FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot)+1,FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot+LEN(B1)+2)-FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot)-1))),&quot;&quot;,TRIM(MID($A1 & &quot;,&quot; & &quot;,&quot;,FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot)+1,FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot+LEN(B1)+2)-FIND(&quot;,&quot;,$A1 & &quot;,&quot; & &quot;,&quot;,FIND(B1,$A1 & &quot;,&quot; & &quot;,&quot)-1)))

I put in the error trapping If statement because otherwise you'll get #Value symbols when you run out of data.

I know it looks ugly but it sure does work. Try it out.

Rob