Text to value using Mid or Split

[gsandy]

I have a column of cells that has either of the following text: “PRECAMBER = 0mm”, “PRECAMBER =12mm” or “PRECAMBER = 135mm”. There will always be the words “Precamber” and “mm”, the numeral portion (ie 0, 12 or 135) is variable.
I wish to use vba to loop down the column and replace the text with a numeric value, in this case 0, 12 or 135, without “Precamber” or “mm”.
I need help in using the “Mid” or “Split” command to achieve this.
Thanks Sandy.

[InvisibleMan]

Why do you need a macro? You could use a formula:

Please Login or Register  to view this content.

[InvisibleMan]

If you do need code, this will apply the formula and convert the results to values:

Please Login or Register  to view this content.

[protonLeah]

Another alternative is to get rid of the text in the cell leaving only the number and use a custom number format:
"PRECAMBER = " # "mm"
for all affected cells.

[InvisibleMan]

@protonleah: he would first have to remove the text from existing data

[gsandy]

Thanks for all the help.
The text could not be removed from existing data as it is an export from a 3D drawing.
Using "SUBSTITUTE" worked a treat, I can now use the result in other calculations.
Thanks Sandy

[InvisibleMan]

You're welcome