Want to put range values in a column into two columns

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

 

[Frank Kabel]

Hi
see your posting in Excel.misc

 

[Arvi Laanemets]

Hi

Easiest is to split your entries into 2 columns. You must have 1 free
columns at right - when not, then insert it. Now select the range with
entries, and then select from menu Data.TexToColumns. Set delimiter to
Other:'-' and press Finish.

Now select range with data in both columns. From menu select Replace, into
Find wath field enter 'MM', and press Replace All button. Done!

The procedure described above works, when all values have same measuring
unit. But when you have entries like 12MM-2CM or 33CM-1.2M, where MM, CM and
M are millimeters, centimeters and meters respectively, then you have to
convert entries using formulas. Here is an example about such a formula for
splitted value (from column A), which converts all values to millimeters,
when they end with "MM" or "CM" or "M", ad returns 0 otherwise:
=IF(RIGHT(A2,2)="MM",1,IF(RIGHT(A2,2)="CM",10,IF(RIGHT(A2)="M",100,0)))*SUBS
TITUTE(SUBSTITUTE(SUBSTITUTE(A2,"MM",""),"CM",""),"M","")
NB! Note, that you have to remove string "M" as last - otherwise you change
all "CM"'s to "C"'s.


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)

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.

 

[Arch]

Frank, I don't see my post in excel.misc. I posted my question in this group thru msdn website. I don't see excel.misc in msdn, I see it in Google but I don't see my post there. Can you please tell me how can I read your post.

 

[Arch]

Thanks a lot Arvi. That works for me.

Hi

Easiest is to split your entries into 2 columns. You must have 1 free
columns at right - when not, then insert it. Now select the range with
entries, and then select from menu Data.TexToColumns. Set delimiter to
Other:'-' and press Finish.

Now select range with data in both columns. From menu select Replace, into
Find wath field enter 'MM', and press Replace All button. Done!

The procedure described above works, when all values have same measuring
unit. But when you have entries like 12MM-2CM or 33CM-1.2M, where MM, CM and
M are millimeters, centimeters and meters respectively, then you have to
convert entries using formulas. Here is an example about such a formula for
splitted value (from column A), which converts all values to millimeters,
when they end with "MM" or "CM" or "M", ad returns 0 otherwise:
=IF(RIGHT(A2,2)="MM",1,IF(RIGHT(A2,2)="CM",10,IF(RIGHT(A2)="M",100,0)))*SUBS
TITUTE(SUBSTITUTE(SUBSTITUTE(A2,"MM",""),"CM",""),"M","")
NB! Note, that you have to remove string "M" as last - otherwise you change
all "CM"'s to "C"'s.


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)



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.

 

[Arch]

One more question. I created a macro for this. It works fine. But I have to say yes to

Do you want to replace the contents of the destination cell

I don't want to get this dialog box when I run the macro. Is there a way to do this.

Thanks.

 

[Andy Brown]

One more question. I created a macro for this. It works fine. But I have
to say yes to

Do you want to replace the contents of the destination cell

If you really want to do this, add the line

Application.DisplayAlerts = False

at the start of your code.

Rgds,
Andy

 

[Arch]

Now, I want to split full name column into three columns, first, middle, last. Some of my full names do not have middle initials. In that scenario, the last name goes in the middle column which I don't want. Is there a way to do it right?

Also I want to delete duplicate names. How do I do this ?

Thanks.