How to extract text when it's included in the format?

[gilliam]

I have a worksheet that contains both numbers and texts. The cells have
units of measurement attached to them, however, they're included in the cell
format.

For example, the cell has 2.0 kg. The format for the cell is custom and
reads #,##0.00 "kg". My worksheet has all sorts of units and I'd like to
pull them aside so that I can convert them. How can I do this if it's
included in the format?

Each unit has it's own cell format - so there's
#,##0.00 "g"
#,##0.00 "%"
and others. This spreadsheet was imported from another system.

I cannot do text to columns as it's a format, not officially text. Any ideas?

I can't

 

[Rick Rothstein]

Can you describe what you mean by "I'd like to pull them aside"? That is,
the cell has 12.34 in it and its format is #,###.00 "kg"... what do want to
"pull aside" from that?

 

[Sheeloo]

You can get the formats in each cell with a macro like this;
'This is just to give an idea...
'This will show the format in each cell in Sheet1 on the corresponding cell
in Sheet2

Sub GetFormat()
Dim i As Integer
Dim j As Integer
For i = 1 To 100
For j = 1 To 20
Sheets("Sheet2").Cells(i, j).Value = Sheets("Sheet1").Cells(i,
j).NumberFormat
Next j
Next i
End Sub

 

[Rick Rothstein]

Okay, based on Sheeloo's post and a re-reading of your subject line, I'm
guessing you want to put the kg from the #,###.00 "kg" format into a cell.
You can use a User Defined Function (UDF) to do that. From the worksheet,
press Alt+F11 to go into the VBA editor and click Insert/Module on its menu
bar, then copy/paste this code into the code window that appeared...

Function GetUnits(C As Range) As String
GetUnits = Replace(Mid(Range("B1").NumberFormat, InStrRev( _
Range("B1").NumberFormat, " ") + 1), """", "")
End Function

Now, go back to the worksheet and (assuming A1 has one of your indicated
cell formats) enter this into a cell (other than A1, of course)...

=GetUnits(A1)

and the units part of the cell format will be displayed.