text and numbers

[JBoulton]

Hi all,

My text has a date, text of various length and words and a number from 0.01
to whatever. Some examples...

'01/05/09 some text goes here 10,000.00
'09/01/09 or 1.05
'05/09/09 or different text like this 250.11

I'd like to create three fields. =datevalue(left(a1,8)) gets the date, but
I'm stuck on the rest.

Thanks for your help.

Jim

 

[Mike H]

Hi

For the rest

=TRIM(MID(A1,9,LEN(A1)))

Mike

 

[Jacob Skaria]

Try the below

=DATEVALUE(LEFT(A1,8))

'to extract text
=LEFT(TRIM(MID(A1,9,255)),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},TRIM(MID(A1,9,255))&"0123456789"))-1)

'to extract the last numeric...You can avoid -- in front to return a text
value
=--MID(TRIM(MID(A1,9,255)),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},TRIM(MID(A1,9,255))&"0123456789")),255)

If this post helps click Yes

 

[JBoulton]

That will create one field with text and the number I want. I need to split
that into two fields, the miscellaneous text and the number at the end.

 

[Mike H]

OOPS I never read your post correctly

To get the text

=TRIM(MID(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",99)),99)))-1),9,256))

to get the last number
=MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)

Mike

 

[Glenn]

Hi all,

My text has a date, text of various length and words and a number from 0.01
to whatever. Some examples...

'01/05/09 some text goes here 10,000.00
'09/01/09 or 1.05
'05/09/09 or different text like this 250.11

I'd like to create three fields. =datevalue(left(a1,8)) gets the date, but
I'm stuck on the rest.

Thanks for your help.

Jim

=--LEFT(A1,8)

=MID(A1,FIND(" ",A1)+1,FIND("~",SUBSTITUTE(A1," ","~",
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

=--RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)

 

[T. Valko]

Does the number *always* have 2 decimal places?

If so, this works on your sample data...

A1 = your string

E1:

=DATEVALUE(LEFT(A1,8))

Format as Date

F1:

=TRIM(MID(SUBSTITUTE(A1,TEXT(G1,"#,##0.00"),""),10,100))

G1:

=--TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

 

[JBoulton]

Jacob,

That's a great solution, but I failed to mention that there could be numbers
mixed in with the middle field text like this.

'01/05/09 some 123 text goes here 987654 10,000.00

Here, I still need three fields.

 

[Jacob Skaria]

OK. Try the below..

=TRIM(MID(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255)),LEN(SUBSTITUTE
(A1," ",REPT(" ",255)))-255)),9,255))

=--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,C1," ")," ",REPT(" ",255)),255))

If this post helps click Yes

 

[JBoulton]

Mike,

I get #VALUE! results with both formulas and my original example data...

 

[JBoulton]

Really close...

The first formula begins with the last digit of the date and then gets the
rest correctly. The second fromula refers to C1. What should be there to
make it work?

 

[Jacob Skaria]

I get the results correctly..Do you have a space infront of the text...If so
replace A1 with Trim(A1)

If this post helps click Yes

 

[JBoulton]

Very nice solution. Thank you.

I get the results correctly..Do you have a space infront of the text...If so
replace A1 with Trim(A1)

If this post helps click Yes

 

[JBoulton]

Nice solution. Thanks.

=--LEFT(A1,8)

=MID(A1,FIND(" ",A1)+1,FIND("~",SUBSTITUTE(A1," ","~",
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

=--RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)
.

 

[JBoulton]

Another nice solution. Thanks to you, too.

Does the number *always* have 2 decimal places?

If so, this works on your sample data...

A1 = your string

E1:

=DATEVALUE(LEFT(A1,8))

Format as Date

F1:

=TRIM(MID(SUBSTITUTE(A1,TEXT(G1,"#,##0.00"),""),10,100))

G1:

=--TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Glenn]

You're welcome.

Nice solution. Thanks.