| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
EXTRACTING NUMBERS FROM A TEXT CELL
Hello!
Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
SSJ wrote:
Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ If the document coming from the accounting program is in fact a text file simply go to file open, and navigate to the file, then open. It should pop up the Text import wizard and allow you to parse the data either with a delimiter or fixed width. If the data is already in the spread sheet go to Data Text to columns and this function will allow you to parse the data. gls858 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Hello gls858
Thanks for the response! (1) When I try opening the text file in Excel give me the following error message: "This file is not in a recognizable format." Therefore I am unable to get it into Excel that way. (2) I tried your suggestion to parse it in Excel by going into Data Text to column and then choosing fixed width. I worked, however, partially. You see the lines are not even, some are long and some are short. It does not quite work when have lines in thousands. Thanks SJ ---- "gls858" wrote in message ... SSJ wrote: Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ If the document coming from the accounting program is in fact a text file simply go to file open, and navigate to the file, then open. It should pop up the Text import wizard and allow you to parse the data either with a delimiter or fixed width. If the data is already in the spread sheet go to Data Text to columns and this function will allow you to parse the data. gls858 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
SSJ wrote:
Hello gls858 Thanks for the response! (1) When I try opening the text file in Excel give me the following error message: "This file is not in a recognizable format." Therefore I am unable to get it into Excel that way. (2) I tried your suggestion to parse it in Excel by going into Data Text to column and then choosing fixed width. I worked, however, partially. You see the lines are not even, some are long and some are short. It does not quite work when have lines in thousands. Thanks SJ ---- "gls858" wrote in message ... SSJ wrote: Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ If the document coming from the accounting program is in fact a text file simply go to file open, and navigate to the file, then open. It should pop up the Text import wizard and allow you to parse the data either with a delimiter or fixed width. If the data is already in the spread sheet go to Data Text to columns and this function will allow you to parse the data. gls858 Is the suffix .txt? It looks like it may be a delimited file with the @ sign as the delimiter. You might try using a space as a delimiter. I see now also that the numbers were brought over as text. I found this program very helpful in converting "text numbers" to actual numbers. It has a lot of other helpful functions also. You can find it at: http://www.asap-utilities.com/ It's a freebie. gls858 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Using Data | Text-to-columns with @ as the delimiter will give you
almost what you need. You might then need to highlight the final column (containing "1< 813,936.29") and use Data | Text-to columns again using < as the delimiter. In answer to your question 1c, =VALUE(RIGHT(A1,11)) would have given you a numeric answer to enable you to carry out arithmetic on it. Hope this helps. Pete SSJ wrote: Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ ------=_NextPart_000_0006_01C6EC75.288A0320 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Google-AttachSize: 2782 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" <HTML<HEAD <META http-equiv=Content-Type content="text/html; charset=iso-8859-1" <META content="MSHTML 6.00.2900.2963" name=GENERATOR <STYLE</STYLE </HEAD <BODY bgColor=#ffffff <DIV<FONT face="Arial Narrow"Hello!</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: </FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV <TABLE style="WIDTH: 516pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=688 border=0 x:str <COLGROUP<FONT face="Arial Narrow" <COL style="WIDTH: 516pt; mso-width-source: userset; mso-width-alt: 22016" width=688</FONT <TBODY <TR style="HEIGHT: 15.75pt" height=21 <TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 516pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=688 height=21 <DIV<FONT face="Arial Narrow"@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1<<SPAN style="mso-spacerun: yes" </SPAN813,936.29</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"There are two things I need to learn he</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"1) How can I extract just the number. </FONT</DIV <DIV<FONT face="Arial Narrow"1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. </FONT</DIV <DIV<FONT face="Arial Narrow"1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. </FONT</DIV <DIV<FONT face="Arial Narrow"1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.</FONT</DIV <DIV </DIV <DIV<FONT face="Arial Narrow"2) How can I parse the data once in Excel, if I want to do that.</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"Thanks in advance</FONT</DIV <DIV<FONT face="Arial Narrow"SJ</FONT</DIV</TD</TR</TBODY</TABLE</DIV</BODY</HTML ------=_NextPart_000_0006_01C6EC75.288A0320-- |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
As long as the "whitespaces" are in fact standard spaces:
=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Hmmm.....
I just noticed something: .......MATERIAL@1< 813,936.29 The number is after the "<" character. If this is the same for all entries then it's even easier: =TRIM(MID(A1,FIND("<",A1)+1,255))*1 Biff "Biff" wrote in message ... As long as the "whitespaces" are in fact standard spaces: =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Biff,
It worked beautifully! Thanks SJ "Biff" wrote in message ... Hmmm..... I just noticed something: ......MATERIAL@1< 813,936.29 The number is after the "<" character. If this is the same for all entries then it's even easier: =TRIM(MID(A1,FIND("<",A1)+1,255))*1 Biff "Biff" wrote in message ... As long as the "whitespaces" are in fact standard spaces: =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Biff,
I was trying to understand the formula. Can you please explain the addition of 1255 & multiplication of 1 in your formula. What is it doing? Thanks SJ "Biff" wrote in message ... Hmmm..... I just noticed something: ......MATERIAL@1< 813,936.29 The number is after the "<" character. If this is the same for all entries then it's even easier: =TRIM(MID(A1,FIND("<",A1)+1,255))*1 Biff "Biff" wrote in message ... As long as the "whitespaces" are in fact standard spaces: =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Pete,
Your suggestion was very helpful in learning about parsing that i did not know before. Thanks SJ "Pete_UK" wrote in message oups.com... Using Data | Text-to-columns with @ as the delimiter will give you almost what you need. You might then need to highlight the final column (containing "1< 813,936.29") and use Data | Text-to columns again using < as the delimiter. In answer to your question 1c, =VALUE(RIGHT(A1,11)) would have given you a numeric answer to enable you to carry out arithmetic on it. Hope this helps. Pete SSJ wrote: Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ ------=_NextPart_000_0006_01C6EC75.288A0320 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Google-AttachSize: 2782 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" <HTML<HEAD <META http-equiv=Content-Type content="text/html; charset=iso-8859-1" <META content="MSHTML 6.00.2900.2963" name=GENERATOR <STYLE</STYLE </HEAD <BODY bgColor=#ffffff <DIV<FONT face="Arial Narrow"Hello!</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: </FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV <TABLE style="WIDTH: 516pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=688 border=0 x:str <COLGROUP<FONT face="Arial Narrow" <COL style="WIDTH: 516pt; mso-width-source: userset; mso-width-alt: 22016" width=688</FONT <TBODY <TR style="HEIGHT: 15.75pt" height=21 <TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 516pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=688 height=21 <DIV<FONT face="Arial Narrow"@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1<<SPAN style="mso-spacerun: yes" </SPAN813,936.29</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"There are two things I need to learn he</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"1) How can I extract just the number. </FONT</DIV <DIV<FONT face="Arial Narrow"1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. </FONT</DIV <DIV<FONT face="Arial Narrow"1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. </FONT</DIV <DIV<FONT face="Arial Narrow"1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.</FONT</DIV <DIV </DIV <DIV<FONT face="Arial Narrow"2) How can I parse the data once in Excel, if I want to do that.</FONT</DIV <DIV<FONT face="Arial Narrow"</FONT </DIV <DIV<FONT face="Arial Narrow"Thanks in advance</FONT</DIV <DIV<FONT face="Arial Narrow"SJ</FONT</DIV</TD</TR</TBODY</TABLE</DIV</BODY</HTML ------=_NextPart_000_0006_01C6EC75.288A0320-- |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
Sure.....
=TRIM(MID(A1,FIND("<",A1)+1,255))*1 The number you want to extract is at the end of the string: .......MATERIAL@1< 813,936.29 Since the "<" character is a unique character and is the last character before the number all we need to do is find that character and extract everything to the right of that character. FIND("<",A1)+1 finds the position of the "<" character and then adds 1. This tells the MID function that that is the starting point of the string we want to extract. 255 is the number of characters that we want to extract. This is just an arbitrary number that is large enough to ensure that we extract all the remaining text to the right of the starting point. So, the string that has been extracted to this point looks like this: =TRIM( 813,936.29)*1 We use the TRIM function to strip out any leading or trailing spaces so that now the string looks like this: 813,936.29 The MID function returns TEXT as its result so the number at this point is TEXT. We use the *1 to coerce the TEXT number into a numeric number so we can use it in other calculations. Biff "SSJ" wrote in message ... Biff, I was trying to understand the formula. Can you please explain the addition of 1255 & multiplication of 1 in your formula. What is it doing? Thanks SJ "Biff" wrote in message ... Hmmm..... I just noticed something: ......MATERIAL@1< 813,936.29 The number is after the "<" character. If this is the same for all entries then it's even easier: =TRIM(MID(A1,FIND("<",A1)+1,255))*1 Biff "Biff" wrote in message ... As long as the "whitespaces" are in fact standard spaces: =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
EXTRACTING NUMBERS FROM A TEXT CELL
You're welcome. Thanks for the feedback!
Biff "SSJ" wrote in message ... Biff, It worked beautifully! Thanks SJ "Biff" wrote in message ... Hmmm..... I just noticed something: ......MATERIAL@1< 813,936.29 The number is after the "<" character. If this is the same for all entries then it's even easier: =TRIM(MID(A1,FIND("<",A1)+1,255))*1 Biff "Biff" wrote in message ... As long as the "whitespaces" are in fact standard spaces: =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1 Biff "SSJ" wrote in message ... Hello! Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below: @1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29 There are two things I need to learn he 1) How can I extract just the number. 1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems. 1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion. 1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths. 2) How can I parse the data once in Excel, if I want to do that. Thanks in advance SJ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
| text and numbers in cell | Excel Worksheet Functions | |||
| How can I remove diff. numbers from a cell combined with text | Excel Discussion (Misc queries) | |||
| Change CSV-load cell format from GENERAL to TEXT for numbers? | Excel Discussion (Misc queries) | |||
| Making calendar with numbers and text in same cell | Excel Discussion (Misc queries) | |||
Linear Mode
