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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Extract part of a cell
 

On Sat, 8 Nov 2008 07:40:01 -0800, André Lopes -Brazil <André Lopes
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.


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

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.

Extract part of a cell
 

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)


"Ron Rosenfeld" wrote in message
...
On Sat, 8 Nov 2008 07:40:01 -0800, André Lopes -Brazil <André Lopes
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.


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

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