| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
refer to text
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
refer to text
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to refer to a text piece in a VBA custom function? | Excel Discussion (Misc queries) | |||
| How can I refer to a worksheet using text in a cell? | Excel Discussion (Misc queries) | |||
| Refer to value | Excel Discussion (Misc queries) | |||
| refer | Excel Discussion (Misc queries) | |||
| Using 'If' refer to specific words in a cell containing text | Excel Worksheet Functions | |||
Linear Mode
