ExcelBanter - refer to text

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - refer to text (https://www.excelbanter.com/excel-discussion-misc-queries/245638-refer-text.html)

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left "n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

Try this:

=MID(A1,3,1)

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left
"n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left "n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

thanks. How about if I want to count it from right?

thanks.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left "n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

Use MID() and LEN()

=MID(A1,Len(A1)-5,1)

Where 5 is the number from the right.

If this helps, please click "Yes"
<<<<<<<<<<

"Lowan Chan" wrote:

thanks. How about if I want to count it from right?

thanks.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left "n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

thanks a lot. It helps. But seems doesn't work on my case. For example:
12,345.60 in A1, in b1, I want to display "0", which is the last digit of
12,345.60. so I set

=mid(a1, len(a1)-0, 1)

the answer comes to "6" in stead of "0". How can I change the formula to get
"0"?

thanks in advance.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left "n"
be displayed in B1, how to make the formula? and if A1 is a number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

Try this:

=RIGHT(TEXT(A1,"###0.00"))

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
thanks a lot. It helps. But seems doesn't work on my case. For example:
12,345.60 in A1, in b1, I want to display "0", which is the last digit of
12,345.60. so I set

=mid(a1, len(a1)-0, 1)

the answer comes to "6" in stead of "0". How can I change the formula to
get
"0"?

thanks in advance.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left
"n"
be displayed in B1, how to make the formula? and if A1 is a number,
e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

Thanks T. Valko. The answer only let me get "0", but cannot get 6, 5.... as
I need to get each digit seperately. Can you help what should I do?

Thanks a lot.
--
Lowan

"T. Valko" wrote:

Try this:

=RIGHT(TEXT(A1,"###0.00"))

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
thanks a lot. It helps. But seems doesn't work on my case. For example:
12,345.60 in A1, in b1, I want to display "0", which is the last digit of
12,345.60. so I set

=mid(a1, len(a1)-0, 1)

the answer comes to "6" in stead of "0". How can I change the formula to
get
"0"?

thanks in advance.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from left
"n"
be displayed in B1, how to make the formula? and if A1 is a number,
e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

.

Excel doesn't recognize terminating 0s in decimal numbers.

The only way you can see them is if you use a number format set to a certain
number of decimal places. For example:

10.00

To Excel, the .00 does not exist. It is only there for *display purposes* IF
you format the cell to display the 2 decimal places. If you select the cell
that contains 10.00 and look in the formula bar you will see10 not 10.00.
That's because those .00 don't really exist.

So, with this in mind perhaps we need a better idea of what you're trying to
do.

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
Thanks T. Valko. The answer only let me get "0", but cannot get 6, 5....
as
I need to get each digit seperately. Can you help what should I do?

Thanks a lot.
--
Lowan

"T. Valko" wrote:

Try this:

=RIGHT(TEXT(A1,"###0.00"))

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
thanks a lot. It helps. But seems doesn't work on my case. For
example:
12,345.60 in A1, in b1, I want to display "0", which is the last digit
of
12,345.60. so I set

=mid(a1, len(a1)-0, 1)

the answer comes to "6" in stead of "0". How can I change the formula
to
get
"0"?

thanks in advance.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word from
left
"n"
be displayed in B1, how to make the formula? and if A1 is a number,
e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

.

You had the answer earlier. Use MID instead of right.
--
David Biddulph

Lowan Chan wrote:
Thanks T. Valko. The answer only let me get "0", but cannot get 6,
5.... as I need to get each digit seperately. Can you help what
should I do?

Thanks a lot.

Try this:

=RIGHT(TEXT(A1,"###0.00"))

--
Biff
Microsoft Excel MVP

"Lowan Chan" wrote in message
...
thanks a lot. It helps. But seems doesn't work on my case. For
example: 12,345.60 in A1, in b1, I want to display "0", which is
the last digit of 12,345.60. so I set

=mid(a1, len(a1)-0, 1)

the answer comes to "6" in stead of "0". How can I change the
formula to get
"0"?

thanks in advance.
--
Lowan

"Jacob Skaria" wrote:

Use MID()

=MID(A1,3,1)

If this post helps click Yes
---------------
Jacob Skaria

"Lowan Chan" wrote:

Hi,

I have a text, e.g. "Finished" in A1, if I want the 3rd word
from left "n"
be displayed in B1, how to make the formula? and if A1 is a
number, e.g.
12,520.70, how can a formula to display "5" in B1?

thanks in advance.
--
Lowan

.