Function (not VB) to remove text from cell

[alepak]

Wondering if anyone can help me with a way to use a function to remove text from a cell. I cannot use VB code, due to the spreadsheet being integrated in an operational software application - already tried it and get nothing but errors from the application. Was also trying to use a Find and replace function, but it's not working.

Here's the scenario - I have a cell that returns the weight of a product from the system, but it includes "LB" or "KG" in the cell along with the weight. I do not know how many characters will be returned to the cell, as it depends on the product. It could be a single digit up to 10 digits, including a decimal point. I need to clean up the cell to only a number, because in the next function, I am trying to round up the number that is left in that cell (won't round up with KG or LB in the way).

Any ideas?

 

[Trebor76]

Hi alepak,

Try this (just adjust the cell reference to suit):

=VALUE(MID(A1,3,LEN(A1)-2))

HTH

Robert

 

[alepak]

Thanks Robert, but it's returning a #VALUE error. I did change the cell as well. But, I'm wondering why the "3" - does that mean it looks to the 3rd character and then trims off the first 2 characters? That won't work. Here's some examples of how the weights will appear in that cell: 101.5 KG, 1205.87 KG, 15422.237 KG
That's where I'm having trouble since there is no clearly defined place where the numbers stop.

 

[alepak]

You know what, I just answered my own question as I typed that out. Obviously, a RIGHT function would work to get rid of the KG, regardless of the amount of digits returned. Gotta quit working on these when I'm deliriously tired! Sorry everyone!

 

[T. Valko]

Thanks Robert, but it's returning a #VALUE error. I did change the cell as well. But, I'm wondering why the "3" - does that mean it looks to the 3rd character and then trims off the first 2 characters? That won't work. Here's some examples of how the weights will appear in that cell: 101.5 KG, 1205.87 KG, 15422.237 KG
That's where I'm having trouble since there is no clearly defined place where the numbers stop.

Here's one way...

=--LEFT(A2,FIND(" ",A2)-1)

Find/Replace should work...

Find what: KG
Replace with: nothing, leave this blank
Replace All


Find what: LB
Replace with: nothing, leave this blank
Replace All

 

[Trebor76]

I assumed (obviously wrongly) that the text looked like "KG123.45". Based the examples you posted, try this formula, again adjusting the cell reference to suit:

=VALUE(LEFT(A1,SEARCH(" ",A1)))

Regards,

Robert