Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split value into 2 cells
I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric)
goes into cell A, and cell B will only retain 234 (numeric). What is the best way to accomplish this? I will be doing this every day, so the most time saving solution will be greatly appreciated. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split value into 2 cells
If you have 1067-234 in B1 then in A1 use
a) if the hyphen is always in the fifth position, =--LEFT(B1,4) b) if the hyphen could be anywhere =--MID(B1,1,FIND("-",B1)-1) Note in both cases the double negation is used to convert text to number A formula cannot alter another cell so we cannot alter B1 Let's insert a new column B so that 1067-234 is in C1 In B1 use a) if the hyphen is always in the fifth position and foloowed by 3 digits, =--MID(C1,6,3) b) if the hyphen could be anywhere =--MID(C1,FIND("-",C1)+1,256) Select A1:B1 (or A1:B100) if you are working with 100 rows of data) ; use the Copy command followed by Edit | Paste Special with Values specified. The formulas will be converted to values and the original data in column C can be deleted best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Vic" wrote in message ... I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric) goes into cell A, and cell B will only retain 234 (numeric). What is the best way to accomplish this? I will be doing this every day, so the most time saving solution will be greatly appreciated. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split value into 2 cells
Select B1 and pull-down:
Data Text to Columns specifiy the - as the separator and make A1 the destination. -- Gary''s Student - gsnu200859 "Vic" wrote: I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric) goes into cell A, and cell B will only retain 234 (numeric). What is the best way to accomplish this? I will be doing this every day, so the most time saving solution will be greatly appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Split the contents of cells across multiple cells | New Users to Excel | |||
Split cells | New Users to Excel | |||
how to split cells? | New Users to Excel | |||
Split cells | Excel Discussion (Misc queries) | |||
split cells | Excel Discussion (Misc queries) |