If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to extract text when it's included in the format?
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 |
#2
|
|||
|
|||
How to extract text when it's included in the format?
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? -- Rick (MVP - Excel) "gilliam" wrote in message ... 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 |
#3
|
|||
|
|||
How to extract text when it's included in the format?
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 -- To get my email id paste my address in an Excel cell and press Enter... "gilliam" wrote: 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 |
#4
|
|||
|
|||
How to extract text when it's included in the format?
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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? -- Rick (MVP - Excel) "gilliam" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|