Cell Text Format Formula

[5656]

Hi.
I know there must be a way to do this within Excel but I haven't been able to locate any resources that help, so hopefully someone here can assist...

I Have the following in a cell (with quotes): "$123.45"
How can I change the format from "$123.45" to simply [red]123.45[/red] ?
I've tried several TEXT functions to remove the quotations and dollar sign but nothing seems to work...any ideas?

Thank you!

 

[mikevh]

Assuming your value is in A1:
=MID(A1,3,LEN(A1)-3)
should do it.

 

[KenWright]

Assuming your data is in A1

=SUBSTITUTE(SUBSTITUTE(A1,"""","&quot,"$","&quot


will return a text string, whereas

=--SUBSTITUTE(SUBSTITUTE(A1,"""","&quot,"$","&quot

will return a value


Regards
Ken..............

 

[ChrisBurch]

Here's a quick & dirty macro that will do it, but there are probably easier ways to acheive the same thing.

Sub convert_to_num()
Dim R, C, maxn As Integer
Dim my_val
Dim my_length ' hold char length of cell contents
R = 1 ' row counter. Set to start ing row
C = 1 ' Column number eg. A = 1, B = 2, C =3
maxn = 7 ' last row to convert

While R <= maxn
Cells(R, C).Select
my_length = Len(Cells(R, C))
my_val = Cells(R, C).Value
my_val = Mid(my_val, 2, (my_length - 2))
my_val = my_val * 1 ' Converts to number
Cells(R, C).Value = my_val
R = R + 1
Wend Chris

It worked yesterday.
It doesn't work today.
That's Windows!