Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
=--MID(A1,FIND("-",A1)+2,99)
"André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
In many case the best way to break up data that is in one cell into two or more cells is to use the Text to Columns Wizard. 1. Select a single column of cells which you want to split. 2. Choose the command Data, Text to Columns, 3. Choose Delimited and click Next 4. Choose one of the delimiter or define one of your own in the Other box, and then click Next 5. In many cases there is nothing you need to do at this step, so just click Finish. If this information helps, please click the Yes button. -- Thanks, Shane Devenshire "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
If your numbers within the cell are always of the form #.### then use this in
B2 =MID(A2,FIND(".",A2)-1,5) "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
if the amount are always at the end of the text as provided in your example,
this will extract all the digits as text Place this formula in B2 and copy down =RIGHT(A2,LEN(A2)-FIND("-",A2)-1) hope this help "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
What did you mean when you said "being that the value could have digits
after it too"? Did that mean you could have more text after the 1.667 some of which could be digits? Or is the value you are after ALWAYS at the end of the text? Also, is there ALWAYS a dash and space in front of the number? -- Rick (MVP - Excel) "André Lopes -Brazil" <André Lopes wrote in message ... Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
Thanks for your help! But, as i said, I never know what will be the number of digits in the cell and that value could have digits after, eg: "Este é um teste e a taxa do USD é USD - 1.667 novamente teste" For the first example the formula works well, but, in second case, not. Can you help again? "Teethless mama" escreveu: =--MID(A1,FIND("-",A1)+2,99) "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
Thanks for your help! I know how to make it manually, but I need this in a formula ... "ShaneDevenshire" escreveu: Hi, In many case the best way to break up data that is in one cell into two or more cells is to use the Text to Columns Wizard. 1. Select a single column of cells which you want to split. 2. Choose the command Data, Text to Columns, 3. Choose Delimited and click Next 4. Choose one of the delimiter or define one of your own in the Other box, and then click Next 5. In many cases there is nothing you need to do at this step, so just click Finish. If this information helps, please click the Yes button. -- Thanks, Shane Devenshire "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
Thanks for your help! But, as i said, I never know what will be the number of digits in the cell and that value could have digits after, eg: "Este é um teste e a taxa do USD é USD - 1.667 novamente teste" Anyhow, in the first example the formula does not works well ... Can you help again? "Sheeloo" escreveu: If your numbers within the cell are always of the form #.### then use this in B2 =MID(A2,FIND(".",A2)-1,5) "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
Thanks for your help! But, as i said, I never know what will be the number of digits in the cell and that value could have digits after, eg: "Este é um teste e a taxa do USD é USD - 1.667 novamente teste" Anyhow, for the first example the formula did not works well ... Can you help again? "franciz" escreveu: if the amount are always at the end of the text as provided in your example, this will extract all the digits as text Place this formula in B2 and copy down =RIGHT(A2,LEN(A2)-FIND("-",A2)-1) hope this help "André Lopes -Brazil" wrote: Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
Hi,
Yes! That is right! I could have more text after the 1.667 and i could find more numbers too. The value could be at the end of the cell or in another position. Ever we can find a dash and space in front of the number. eg: "Este é um teste e a taxa do USD é USD - 1.667 novamente teste" Can you help again? "Rick Rothstein" escreveu: What did you mean when you said "being that the value could have digits after it too"? Did that mean you could have more text after the 1.667 some of which could be digits? Or is the value you are after ALWAYS at the end of the text? Also, is there ALWAYS a dash and space in front of the number? -- Rick (MVP - Excel) "André Lopes -Brazil" <André Lopes wrote in message ... Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
As long as there is a dash/space in front of the number (and as long as
there is no other dash/space earlier in the text), this should work... =LOOKUP(9E+307,--LEFT(MID(A1,FIND("- ",A1)+2,99),ROW($1:$99))) -- Rick (MVP - Excel) "André Lopes -Brazil" wrote in message ... Hi, Yes! That is right! I could have more text after the 1.667 and i could find more numbers too. The value could be at the end of the cell or in another position. Ever we can find a dash and space in front of the number. eg: "Este é um teste e a taxa do USD é USD - 1.667 novamente teste" Can you help again? "Rick Rothstein" escreveu: What did you mean when you said "being that the value could have digits after it too"? Did that mean you could have more text after the 1.667 some of which could be digits? Or is the value you are after ALWAYS at the end of the text? Also, is there ALWAYS a dash and space in front of the number? -- Rick (MVP - Excel) "André Lopes -Brazil" <André Lopes wrote in message ... Hi, My name is André Lopes and i am writing from Brazil. I have a big problem and i am nedding help ... I have in a cell (For example "A2") the following: "Este é um teste e a taxa do USD é USD - 1.667" And i need to extract only the value that will be, ever, *.***, where * means any number. Please note that i never know what will be the number of digits in the cell, being that the value could have digits after it too. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract part of a cell
On Sat, 8 Nov 2008 17:23:01 -0500, "Rick Rothstein"
wrote: To the OP: This formula would need the double unary in front of it if the extracted value is to be a number and not text... =--LEFT(TRIM(MID(A1,FIND("-",A1)+1,99))&" ", FIND(" ",TRIM(MID(A1,FIND("-",A1)+1,99))&" ")-1) -- Rick (MVP - Excel) Again, to the OP: Be aware that the double unary will only work properly for you if the number in your text string is formatted the same as is expected on your Windows Regional Settings (in Control Panel). The US uses the dot as a decimal symbol; and the comma as a digit grouping symbol. In Portuguese these are reversed. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract numeric part of alphanumeric cell | Excel Worksheet Functions | |||
Is there a function to extract the URL part of a hyperlink in another cell? | Excel Worksheet Functions | |||
Can I search a cell for a value and extract part of content? | Excel Discussion (Misc queries) | |||
Extract Part of String | Excel Worksheet Functions | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) |