×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Removing Copied Text From Numbers

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

RE: Removing Copied Text From Numbers

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.

RE: Removing Copied Text From Numbers

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

RE: Removing Copied Text From Numbers

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!

RE: Removing Copied Text From Numbers

You could also use Edit/find and replace

tg

RE: Removing Copied Text From Numbers

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.

RE: Removing Copied Text From Numbers

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources