Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format split
Hi
I have one big excel sheet. in this sheet one column use many custom format. when i type any number that column, unit display with number. for example; A B C 100 pcs 50 LTR 200 carton 60 pcs .... in column A use many cutom format like ""0" PCS" , ""0" LTR" I wants split number and unit in column B and C PLease help me urgently RKS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
format split
First, we need a user defined function to extract the cell format. Open up
the VBE (Alt+F11) and goto insert - module. Paste the following in: '======= Function MyFormat(Reference As Range) As String MyFormat = Reference.NumberFormat End Function '======= Back in your workbook, the formula in column B is: =A2 Formula in column C: =SUBSTITUTE(MID(MyFormat(A2),4,999),"""","") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RKS" wrote: Hi I have one big excel sheet. in this sheet one column use many custom format. when i type any number that column, unit display with number. for example; A B C 100 pcs 50 LTR 200 carton 60 pcs ... in column A use many cutom format like ""0" PCS" , ""0" LTR" I wants split number and unit in column B and C PLease help me urgently RKS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format split
I have two options for you which may vary depending on which version you are
running. 1) If all the data is entered in Column A and you just need to fill in B & C there is an option under Data- Text to Columns. Using this wizard it will split your data into columns. 2) If you need columns B & C to update as you are entering A: Column B formula: =LEFT(A1,SEARCH(" ",A1,1)) Column C formula: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)) Note that these formulas are grabbing the left and right data based on the space (" ") if you enter an item such as "John M. Doe" it will not pull it properly. "RKS" wrote: Hi I have one big excel sheet. in this sheet one column use many custom format. when i type any number that column, unit display with number. for example; A B C 100 pcs 50 LTR 200 carton 60 pcs ... in column A use many cutom format like ""0" PCS" , ""0" LTR" I wants split number and unit in column B and C PLease help me urgently RKS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split | Excel Discussion (Misc queries) | |||
Split a cell diagonally and apply a different conditional format. | Excel Worksheet Functions | |||
How do I remove split a split window? | New Users to Excel | |||
2nd split | Excel Discussion (Misc queries) | |||
Split box | New Users to Excel |