If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Want to put range values in a column into two columns
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.
|
#2
|
|||
|
|||
Want to put range values in a column into two columns
Hi
see your posting in Excel.misc -- Regards Frank Kabel Frankfurt, Germany Arch wrote: 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. |
#3
|
|||
|
|||
Want to put range values in a column into two columns
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(R IGHT(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) "Arch" wrote in message ... 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. |
#4
|
|||
|
|||
Want to put range values in a column into two columns
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.
"Frank Kabel" wrote: Hi see your posting in Excel.misc -- Regards Frank Kabel Frankfurt, Germany Arch wrote: 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. |
#5
|
|||
|
|||
Want to put range values in a column into two columns
Thanks a lot Arvi. That works for me.
"Arvi Laanemets" wrote: 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(R IGHT(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) "Arch" wrote in message ... 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. |
#6
|
|||
|
|||
Want to put range values in a column into two columns
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. "Arvi Laanemets" wrote: 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(R IGHT(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) "Arch" wrote in message ... 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. |
#7
|
|||
|
|||
Want to put range values in a column into two columns
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 |
#8
|
|||
|
|||
Want to put range values in a column into two columns
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. "Arvi Laanemets" wrote: 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(R IGHT(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) "Arch" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|