Split string using Excel

Question

I have a string with the following format:

StockCode Country Date Price equity

for example:

1 hk 10/31/12 C70.5 equity
101 hk 11/21/13 P63 equity
388 hk 10/17/12 P100 equity

I can extract Date by this Excel Command:

LEFT(RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))),FIND(" ",
RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1))))))

(line breaks for readbility)

result: 10/31/12

Anyone has a better solution for this?

here are some of my codes:

how to get "C" and "P":

TRIM(MID(A1,IFERROR(FIND("P",A1),FIND("C",A1)),1))

how to get the strike price:

TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-IFERROR(FIND("P",A1),
FIND("C",A1))),"equity",""))

andrewsi · Accepted Answer · 2012-10-10 14:02:10Z

14

Try this, I think it should work for all dates with a 2 character year:

=TRIM(MID(A1,FIND("/",A1)-2,8))

edited Oct 10, 2012 at 14:02

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

answered Oct 10, 2012 at 13:58

John

1642 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Excellll Over a year ago

This also assumes that both month and day are also always two characters (i.e. all dates are mm/dd/yy), and that StockCode and Country will never contain a "/".

littlecodefarmer758 Over a year ago

Thanks John, Finally DATEVALUE(Date_type_like_TEXT) is requited to change the extracted string to be date, eg, mm/dd/yyyy

Daniel · Accepted Answer · 2012-10-10 14:12:50Z

4

I'd recommend running Text to Columns from the Data tab. Choose Delimited, and indicate space as the delimiter. This will put your string into five columns with the third one being date, and then you can freely use the data directly.

Otherwise, I'd look at Lajos Example:( =TRIM(MID(A1,FIND("!^!",SUBSTITUTE(A1," ","!^!",2)),8)) ) or John's answer.

answered Oct 10, 2012 at 14:12

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Comments

Jamie Bull · Accepted Answer · 2013-10-25 00:48:27Z

4

If you can use a macro UDF this is simple.

Function STR_SPLIT(str as String, sep as String, n as Long)
    Dim Arr() as String
    Arr = Split(str, sep)
    STR_SPLIT = Arr(n - 1)
End Function

str is the string to split, sep is the character to split on - " " in your case - and n is the column number you're looking for. So =STR_SPLIT(A1," ",3) would return the date for example.

edited Oct 25, 2013 at 0:48

answered Oct 10, 2012 at 18:05

Jamie Bull

13.6k18 gold badges80 silver badges123 bronze badges

Comments

seanjacob · Accepted Answer · 2013-04-05 13:53:10Z

3

You could split each part in to columns in Excel by going to Data > Text to Columns and using the space as a delimiter.

edited Apr 5, 2013 at 13:53

answered Apr 5, 2013 at 13:47

seanjacob

2,2581 gold badge23 silver badges26 bronze badges

1 Comment

markokstate Over a year ago

Solved a random issue of mine. Great recommendation.

Lajos Arpad · Accepted Answer · 2012-10-10 13:57:08Z

1

Your date is between the second and the third space in all your examples. You can replace your third space with "!^!" for example and then get the substring until the first occurrence of "!^!". After that you just have to extract the substring after the second space. Kindly read more here.

answered Oct 10, 2012 at 13:57

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

Comments

barry houdini · Accepted Answer · 2012-10-10 14:27:22Z

1

This formula should also give the required result, even if year has 4 characters

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,99))

answered Oct 10, 2012 at 14:27

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

4 Comments

Daniel Over a year ago

This would also work. =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10000)),15000,10000)) +1 for providing a silly yet valid answer.

barry houdini Over a year ago

Thanks for the +1 Daniel but not sure why you feel it's "silly" - in what way?

Daniel Over a year ago

Because it depends on stuffing the original string with lots of whitespace and then retrieving the desired information imprecisely. Granted, it's probably not silly, I suppose novel would be more appropriate. Sorry. Not that this matters, but your formula only returns the first 98 characters of the third term (Assuming the first and second term are 0 length). Less the total of the lenght of the first and second term. If the first and second term combined were more than than 97 characters long, the third term will not be returned at all because you aren't using enough whitespace filler.

barry houdini Over a year ago

I concede it was tailored based on the example strings provided, perhaps it would be better as =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1))) the 2 can be changed depending on which text one wants to retrieve

littlecodefarmer758 · Accepted Answer · 2012-10-10 14:28:14Z

0

Thanks John

here are some of my codes:

how to get "C" and "P":

TRIM(MID(A1,IFERROR(FIND("P",A1),FIND("C",A1)),1))

how to get the strike price:

TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-IFERROR(FIND("P",A1),FIND("C",A1))),"equity",""))

answered Oct 10, 2012 at 14:28

littlecodefarmer758

9762 gold badges10 silver badges25 bronze badges

Collectives™ on Stack Overflow

Split string using Excel

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related