Contact US

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!

*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

Splitting a cell in two

Splitting a cell in two

Splitting a cell in two

I would like to split the contents of a cell into 2 parts (currently, I have a column of entrys that denote a distance - i.e, 40m
                50m ....etc)
Is there a way to split these cells so that the number number is in one cell and the 'm' is in the other? (a reverse 'concatenate' function?)   

RE: Splitting a cell in two

there at least one way:

In the adjacant collum write the formula:

This will give you the numerical value in this collum (<cell> refers to the cell where your original text string is).

I assume that there is allways only one letter (m) but that the could be 1,2,3 or more digits.

Best regards


RE: Splitting a cell in two

You can consider using the Data|Text to columns method.


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Splitting a cell in two

Thanks MortenA but if I wanted to export the two segments to the cell (i.e., the number and the unit) to two different cells, is there an easy way to do this?

RE: Splitting a cell in two

Check out the LEFT, and RIGHT functions....

RE: Splitting a cell in two

When you do split it with the Left & Right function check if the numbers would be interpreted as numbers & allow you do to math functions & lookup functions. I did a similar work where code numbers for company type & company type description were in one cell. After spliting them up I worked on another spreadsheet with code nos. I created a vlookup fuction to return the company type description corresponding to the code number. It did not work because the code numbers were interpreted as text not as numbers. I formated them as numbers but it did not work. I multiplied them by one (suprisingly it allowed this arthmitic operation) and entered the product on another column of cells. I then formated these cells as numbers then range value copied them over the code numbers in the original cell. The vlookup finally worked after that.

RE: Splitting a cell in two

To ensure that the left part is always treated as a number, use:


Otherwise, if you do sorts or lookups, it will be treated as text.

RE: Splitting a cell in two

I only assumed that all units where "m" - in order to simplify things i though that you could just copy past this letter into any cell that you wanted.

Using the value funtion is OK but should not matter unless then cells used for the formula are formatted as text.

Best reagards


RE: Splitting a cell in two

If you are trying to separate the unit from the value just because you want to be able to use the values for calculation, then it may be easier to custom format the cells to show "m" as a unit. You won't need to cells then.

Use <Format Cells><Number><custom>
enter 0.00 "m" in the type box

That may be a bit simpler than trying to split cells.

To get rid of all the "m"'s, in the original column, just do a replace, replacing "m" with nothing.


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! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close