How to remove the last letter from a text field.

Jbob

I have a list of data:

8888 H

88 H

888 M

8.888 M

How do I remove the H & M to leave a number that I can use in a calculation?

Paul Newcome

Try wrapping it in a VALUE function.

=VALUE(original_formula)

Nick Korna

Hi @Jbob,

If your data always has the extra letter in it, then this would work:

=LEFT(Data@row, (LEN(Data@row) - 1))

If you don't always have the extra letter, or have variable length after the space, then something like this is better:

=IFERROR(LEFT(Data@row, (FIND(" ", Data@row) - 1)), Data@row)

Hope this helps! 😊

Jbob

Hello Nick,

That is great, it gives me the number, however it is not a number that I can then use in a calculation.

The calculation returns #INVALID OPERATION.

When I past 'values' it has a hidden 'in front of the number. i.e. '10 instead of 10.

Any more suggestions?

Thank you

Paul Newcome

Try wrapping it in a VALUE function.

=VALUE(original_formula)

Jbob

Perfect!

Thank you!

Paul Newcome

Happy to help. 👍️