| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) . |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing A Column Of Text numbers To True Numbers: How ? | New Users to Excel | |||
| how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
| VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
| Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Linear Mode
