Removing Copied Text From Numbers 1

[Verner]

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&quot 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???

 

[melone]

Use the trim() functions (to guarenetee only 1 space between groups of characters) and the left() function to strip off the first X number of characters (assuming a standard number of digits). Otherwise, you will have to count the number of charcters up the space, and strip off that many using the left() function.

 

[IRstuff]

The other choice is the "Text to Columns" option on the Data menu. Since your text is separated from the numbers by a space, you can either use fixed column format or delimited data format to separate everything.

TTFN

 

[Mutt]

Alternatively :
=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!

 

[trainguy]

You could also use Edit/find and replace

tg

 

[HumbleMan]

If the data starts in A2, try the following in B2 and all remaining cells in the column:

=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.

 

[mroberts]

I think the easiest way is similar IRstuff's idea. Save the file as a .txt file. Then open Excel, do File, Open, Files of Type "All Files" (*.*) and pick the saved .txt file. The Text Import Wizard window comes up (at least in Office 97 it does). Pick Delimited, Next, check "Space", move the divider to wherever you want it and pick Finish. Voila.