Removing symbols from Numbers

[chris]

Hello,
I have a problem with removing objects from numbers. The
first problem is that I would like to remove a # symbol
and just get the real value.
For example I have values of #4 and #15 and would like it
to read just 4 and 15.

The other problem is that I have numbers such as 185/10
and 45/0 and so forth. I would only want the whole value
185 and 45 and not the symbol / or anything after it.
Can you help me.

Thank you

Chris

 

[Frank Kabel]

Hi Chris,
for numbers like '#12345676' try the following:
=VALUE(MID(A1,2,14))

for numbers like '12345/12344' try
=VALUE(LEFT(A1,FIND("/",A1)-1))

Frank

 

[Jonathan Rynd]

for numbers like '#12345676' try the following:
=VALUE(MID(A1,2,14))

You don't need the 14. If you leave off the last argument, Excel copies
everything all the way to the end of the string.

=VALUE(MID(A1,2))

 

[Harlan Grove]

...
...

You don't need the 14. If you leave off the last argument, Excel copies
everything all the way to the end of the string.

=VALUE(MID(A1,2))

VBA's Mid function has an optional 3rd argument, but Excel's MID worksheet
function requires a 3rd arg. Try entering your formula as-is into any cell.

 

[Frank Kabel]

Hi Jonathan
won't do in Excel's worksheet function. Only true for VBA

Frank

 

[Jonathan Rynd]

VBA's Mid function has an optional 3rd argument, but Excel's MID
worksheet function requires a 3rd arg. Try entering your formula as-is
into any cell.

Thanks, I remembered seeing it in help but I guess I was looking in VB
help.

 

[Jonathan Rynd]

Hi Jonathan
won't do in Excel's worksheet function. Only true for VBA

Thanks, I remembered seeing it in help but I guess I was looking in VB
help.