format split

[RKS]

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

 

[Luke M]

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),"""","")

 

[Sninkle]

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.