Want to put range values in a column into two columns

  • Thread starter Thread starter Arch
  • Start date Start date
A

Arch

In an excel spreadsheet I have a column with range values like
10MM-24.9MM. What I want to do is split that column into two columns:
Minimum and Maximum. In my example minimum will have a value of 10 and
maximum will have a value of 24.9, just want to have numeric values.
How can I do this ? I am not an excel expert, I will appreciate if
someone can tell me the steps to do this. I am sorry if this is a very
simple question for this group.
 
Hi Arch!

Set up two helper columns and use the following formula assuming your
first entry is in A1:

=LEFT(A1,FIND("M",A1)-1)
=LEFT(MID(A1,FIND("-",A1)+1,255),FIND("M",MID(A1,FIND("-",A1)+1,255))-1)

Now copy these down as far as your data goes.
 
Hi
a different approach would be:
- use 'Data - Text to columns and use the '-' as delimiter
- After this goto 'Edit - Replace' and replace 'MM' with nothing
 
Hi Arch..........

First, if your data is in column A, make sure column B is blank, then
highlight the whole column A by leftclicking on the "A" at the top
Then left click on "DATA" from the toolbar at the top
Then select "Text to columns" and left click on it
Then make sure the button is checked for "Delimited" and click on "Next"
Then left-click on the box at the left of "Other" to select it and type a
hyphen in the box to the right of "Other", and click "Finish"

This will have separated your data into two columns, Column A will have 10MM
and column B will have 24.9MM and the hyphen will have disappeared.

Now, highlight both columns by left-clicking on the A at the top of the
column and holding the button down and dragging it across to the B..........

Now, Left-click on the EDIT selection in the top toolbar, choose the
"REPLACE" option and left-click it.........

Now, in the "Find What:" box, type MM, and leave the "Replace with:" box
empty and click on "Replace all:".........

This makes the MM disappear............now you have 10 in column A and 24.9
in column B, and respectively the same separation in all rows below.......

Vaya con Dios,
Chuck, CABGx3
 
Back
Top