ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   text and numbers (https://www.excelbanter.com/excel-worksheet-functions/246129-text-numbers.html)

JBoulton
text and numbers
 
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
text and numbers
 
Hi

For the rest

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

Mike

"JBoulton" wrote:

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

Jacob Skaria
text and numbers
 
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
---------------
Jacob Skaria


"JBoulton" wrote:

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

JBoulton
text and numbers
 
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" wrote:

Hi

For the rest

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

Mike

"JBoulton" wrote:

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
text and numbers
 
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" wrote:

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

Glenn
text and numbers
 
JBoulton wrote:
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
text and numbers
 
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


"JBoulton" wrote in message
...
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



JBoulton
text and numbers
 
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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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

Jacob Skaria
text and numbers
 
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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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

JBoulton
text and numbers
 
Mike,

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

"Mike H" wrote:

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" wrote:

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

JBoulton
text and numbers
 
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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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

Jacob Skaria
text and numbers
 
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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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

JBoulton
text and numbers
 
Very nice solution. Thank you.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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" wrote:

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
---------------
Jacob Skaria


"JBoulton" wrote:

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

JBoulton
text and numbers
 
Nice solution. Thanks.

"Glenn" wrote:

JBoulton wrote:
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)
.

JBoulton
text and numbers
 
Another nice solution. Thanks to you, too.

"T. Valko" wrote:

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


"JBoulton" wrote in message
...
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


.

T. Valko
text and numbers
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JBoulton" wrote in message
...
Another nice solution. Thanks to you, too.

"T. Valko" wrote:

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


"JBoulton" wrote in message
...
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


.

Glenn
text and numbers
 
You're welcome.

JBoulton wrote:
Nice solution. Thanks.

"Glenn" wrote:

JBoulton wrote:
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)
.


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com