Extract part of a cell

[André Lopes -Brazil]

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.

 

[Teethless mama]

=--MID(A1,FIND("-",A1)+2,99)

 

[ShaneDevenshire]

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.

 

[Sheeloo]

If your numbers within the cell are always of the form #.### then use this in
B2
=MID(A2,FIND(".",A2)-1,5)

 

[franciz]

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

 

[Rick Rothstein]

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?

 

[André Lopes -Brazil]

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:

 

[André Lopes -Brazil]

Hi,

Thanks for your help!
I know how to make it manually, but I need this in a formula ...

"ShaneDevenshire" escreveu:

 

[André Lopes -Brazil]

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:

 

[André Lopes -Brazil]

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:

 

[André Lopes -Brazil]

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:

 

[Ron Rosenfeld]

On Sat, 8 Nov 2008 07:40:01 -0800, André Lopes -Brazil <André Lopes

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.

Because of the examples you have provided in subsequent posts, I think you may
be misusing the term "digits" (digitos ou numera).

If there are truly "digitos" after the number you are trying to extract, we
need some examples so as to figure out a way to differentiate.

From what you have provided, it appears as if the string you are looking for is
the first word after a hyphen.

If that is the case, it can be extracted with this formula:

=LEFT(TRIM(MID(A1,FIND("-",A1)+1,99))&" ",
FIND(" ",TRIM(MID(A1,FIND("-",A1)+1,99))&" ")-1)



--ron

 

[Rick Rothstein]

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 Rothstein]

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)

 

[Ron Rosenfeld]

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)

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