FORMULA TO REMOVE TEXT FROM A CELL

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

I have some entries like:

10 Kg
15 Kilo
12
13.5 Kilogram

How, to have all the text be removed from the same having the numericals
remained presented as:

10
15
12
13.5
 
Hi

Use this formula in a helper column, assuming 12 is a true value:

=IF(ISERROR(VALUE(A1)),VALUE(LEFT(A1,FIND("K"A1)-2))A1)

Regards,
Per
 
I think this process give you what I think you ultimately want to end up
with. Select all the cells in the column from the first value to the last
value in the column (it is okay to select the mixture of cells with text and
cells with just numbers in them), click Edit/Replace from the menu bar, type
a space followed by and asterisk in the "Find what" field, leave the
"Replace with" field empty, then hit the "Replace All" button.
 
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

--
Best Regards,

Faraz


Rick Rothstein said:
I think this process give you what I think you ultimately want to end up
with. Select all the cells in the column from the first value to the last
value in the column (it is okay to select the mixture of cells with text and
cells with just numbers in them), click Edit/Replace from the menu bar, type
a space followed by and asterisk in the "Find what" field, leave the
"Replace with" field empty, then hit the "Replace All" button.
Click to expand...
 
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

--
Best Regards,

Faraz


David Biddulph said:
=IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1))
Click to expand...
 
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

--
Best Regards,

Faraz



Per Jessen said:
Hi

Use this formula in a helper column, assuming 12 is a true value:

=IF(ISERROR(VALUE(A1)),VALUE(LEFT(A1,FIND("K"A1)-2))A1)

Regards,
Per
Click to expand...
 
That'll need VBA, not a formula.
--
David Biddulph

Faraz A. Qureshi said:
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234
Click to expand...
 
Then, select the cells to be converted and run this macro:

Sub ExtractNumber()
Dim MyVal As Long
Dim n As Long

For Each cell In Selection.Cells
For n = 1 To Len(cell)
If IsNumeric(Mid(cell, n, 1)) Then
MyVal = MyVal & Mid(cell, n, 1)
End If
Next
cell.Value = MyVal
MyVal = 0
Next
End Sub

Regards,
Per

Faraz A. Qureshi said:
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234
Click to expand...
 
Mike H's following Array formula turned out to be great:

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)
 
Then you gave us bad examples because all your original examples showed a
number followed by text and indicated you wanted the number before the
space.

--
Rick (MVP - Excel)


Faraz A. Qureshi said:
I am sorry what I sought was to have all the text be removed. Like:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234
Click to expand...
 
This macro, a slight modification of Per Jessen's macro to preserve the
period, will do that. HTH Otto
Sub ExtractNumber()
Dim MyVal As String
Dim n As Long
Dim cell As Range
For Each cell In Selection.Cells
MyVal = ""
For n = 1 To Len(cell)
If IsNumeric(Mid(cell, n, 1)) Or _
Mid(cell, n, 1) = "." Then
MyVal = MyVal & Mid(cell, n, 1)
End If
Next
cell.Value = MyVal
MyVal = 0
Next
End Sub
 
Back
Top