How to split units from numbers stored in a cell that is custom formatted

[jhilamroy]

Hi All! I am new to this forum. Need help.

I need to separate units from quantities. i am not being able to do it because cells that are custom formatted and no text function works on these. When converted to text format, the units automatically disappears! Can you please help me out on this.

 

[RoryA]

If the units are only part of the custom format, you'd need code to retrieve them - do you need them, or the quantities, or both?

 

[mohadin]

In this case try the code

Sub test()
    a = Range("a1").Text
    a = Split(a, Left(a, 1))
    Cells(1, 2) = a(1)
End Sub

Copy the code
Alt+f11
Insert>module> paste
Go Back to excel sheet>view>macros>view macros>select test and hit Run

 

[jhilamroy]

Thanks Rory for quick response! Yes the units are part of the custom format. Any non-code solution would have been better no? If not possible, then please share the code and guide how to apply it on my worksheet please!

 

[jhilamroy]

Thanks for the code! but not solving my problem.

I have something like the following as my input:

ITEM QUANTITY
A 0.27 Tons
B 0.35 MT
C 300.5748 METER
B 65.248 Litre

And the output expected will be like this:

ITEM QUANTITY UoM
A 0.27 Tons
B 0.35 MT
C 300.5748 METER
B 65.248 Litre

Now since the QUANTITY field is custom formatted, no function works on them. That is my issue.

 

[mohadin]

Well in you case
data in column A

Sub test()
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
          a = Split(Range("a" & i).Text, " ")
          Cells(i, 2) = a(0) & " " & a(1)
    Next
End Sub

 

[mohadin]

Or may be

Sub test()  
  lr = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lr
        a = Split(Range("a" & i).Text, " ")
        Cells(i, 2) = a(0) & " " & a(1)
        Cells(i, 3) = a(2)
    Next
End Sub

 

[Rick Rothstein]

I have something like the following as my input:

ITEM QUANTITY
A 0.27 Tons
B 0.35 MT
C 300.5748 METER
B 65.248 Litre

So are you saying Column A has letters like A, B, C in it and Column B has your formatted numbers with each Column's values starting on Row 2 (headers being on Row 1)? If so, give this macro a try...

Sub SplitFormattedValues()
  Dim Cell As Range, ValQuant() As String
  For Each Cell In Range("B2", Cells(Rows.Count, "B").End(xlUp))
    Cell.Offset(, 1) = Split(Cell.Text & " ")(1)
    Cell.ClearFormats
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SplitFormattedValues) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[salim hasan]

What about this macro

Option Explicit
Sub test()
Dim obj As Object, lr%, i%, Ys
Set obj = CreateObject("VBScript.RegExp")
lr = Cells(Rows.Count, 1).End(xlUp).Row
obj.Pattern = "(\.\d*)"
  For i = 1 To lr
    If obj.test(Range("a" & i)) Then
     Set Ys = obj.Execute(Range("a" & i))
      Range("b" & i) = Ys(0)
    End If
  Next i
End Sub

 

[salim hasan]

What About This Macro

Option Explicit
Sub test()
Dim obj As Object, lr%, i%, I_find


Set obj = CreateObject("VBScript.RegExp")
lr = Cells(Rows.Count, 1).End(xlUp).Row
obj.Pattern = "(\.?\d+)"
  For i = 1 To lr
    If obj.test(Range("a" & i)) Then
     Set I_find = obj.Execute(Range("a" & i))
          Range("b" & i) = I_find(0).Value
    End If
  Next i
End Sub

I know that I should delete my first post
But I don't know how
Please Explain