Removing Copied Text From Numbers
Removing Copied Text From Numbers
(OP)
I've run into a problem where some of the old boys at work have built some spreadsheets with a lot of data. The problem is that they've keyed in a substantial amount of data that and manually keyed in the measured units (in this case "m/day") in every cell instead of formatting it after they typed in the numerical value)
i.e.
4.5 m/day
2.1 m/day
5.0 m/day.....
How do I convert this to a numerical value so that I can omit the "m/day" portion and use the number portion to calculate my advances???
i.e.
4.5 m/day
2.1 m/day
5.0 m/day.....
How do I convert this to a numerical value so that I can omit the "m/day" portion and use the number portion to calculate my advances???





RE: Removing Copied Text From Numbers
RE: Removing Copied Text From Numbers
TTFN
RE: Removing Copied Text From Numbers
=IF(ISERROR(FIND("m",D17)),D17,VALUE(TRIM(LEFT(D17,FIND("m",D17)-1))))
where the entered value is in cell D17 for example
Error trap there just in case the 'old boy' didn't enter m/day for some values!
RE: Removing Copied Text From Numbers
tg
RE: Removing Copied Text From Numbers
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)
It errs to "#VALUE" if there is no space between the number and the units. This method also ignores typos if "m" isn't entered.
RE: Removing Copied Text From Numbers