text and numbers

  • Thread starter Thread starter JBoulton
  • Start date Start date
J

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
 
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
 
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.
 
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
 
JBoulton said:
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
Click to expand...


=--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)
 
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))
 
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.
 
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
 
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?
 
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
 
Very nice solution. Thank you.

Jacob Skaria said:
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
Click to expand...
 
Nice solution. Thanks.

Glenn said:
=--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)
.
Click to expand...
 
Another nice solution. Thanks to you, too.

T. Valko said:
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





.
Click to expand...
 
Back
Top