Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mixed Text & Numbers
I have a row of numbers, which can be 2 or 3 digits long. Some have * before
the numbers, some don't. I want another cell to be able to read the numeric value if the cell has an * before it. Any ideas? I'm stuck. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mixed Text & Numbers
=IF(LEFT(A2)="*",--RIGHT(A2,LEN(A2)-1),A2)
-- David Biddulph "Mike" wrote in message ... I have a row of numbers, which can be 2 or 3 digits long. Some have * before the numbers, some don't. I want another cell to be able to read the numeric value if the cell has an * before it. Any ideas? I'm stuck. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mixed Text & Numbers
I want another cell to be able to read the numeric value if the cell
has an * before it. Any ideas? If you mean that only for cells that have "*", then the formula given above wont do because it will give you the numerics after the "*" in case there is a star. In case there is no star, it will return the value in the cell, but this doesnt seem to be what you exactly want unless i misunderstood you, but in the way you are asking "... value if the cell has an * before it" means only cells with "*", if this is the case than use this: =IF(LEFT(A1,1)="*",--MID(A1,2,LEN(A1)),"") this will return the value after the "*" if there is a star, and nothing if no star was found. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mixed Text & Numbers
Maybe this:
=--SUBSTITUTE(A1,"*","") -- Biff Microsoft Excel MVP "Mike" wrote in message ... I have a row of numbers, which can be 2 or 3 digits long. Some have * before the numbers, some don't. I want another cell to be able to read the numeric value if the cell has an * before it. Any ideas? I'm stuck. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The order of numbers mixed with letters | Excel Discussion (Misc queries) | |||
how do I sort a column mixed with odd and even numbers .. ? | Excel Discussion (Misc queries) | |||
Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
Sort mixed numbers/letters | Excel Discussion (Misc queries) | |||
Incrementing Mixed text & numbers | Excel Discussion (Misc queries) |