| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA TO REMOVE TEXT FROM A CELL
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 -- Best Regards, Faraz |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
=IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1))
-- David Biddulph "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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 "Faraz A. Qureshi" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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. -- Rick (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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" wrote: 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. -- Rick (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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" wrote: =IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1)) -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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" wrote: 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 "Faraz A. Qureshi" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
That'll need VBA, not a formula.
-- David Biddulph "Faraz A. Qureshi" wrote in message ... 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" wrote: =IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1)) -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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" skrev i meddelelsen ... 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" wrote: 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 "Faraz A. Qureshi" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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) -- Best Regards, Faraz "David Biddulph" wrote: That'll need VBA, not a formula. -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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" wrote: =IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1)) -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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" wrote in message ... 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" wrote: 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. -- Rick (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
I thought you said that for 13.5 Kilogram you wanted the result to be 13.5?
Doesn't that formula return 135? -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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) -- Best Regards, Faraz "David Biddulph" wrote: That'll need VBA, not a formula. -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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" wrote: =IF(ISNUMBER(A1),A1,--LEFT(A1,FIND(" ",A1)-1)) -- David Biddulph "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
FORMULA TO REMOVE TEXT FROM A CELL
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 "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... 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" wrote: 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 "Faraz A. Qureshi" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove text from cell | Excel Worksheet Functions | |||
| Formula to remove certain text | Excel Worksheet Functions | |||
| Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
| Remove some text from a cell | Excel Worksheet Functions | |||
| remove text from cell | Excel Discussion (Misc queries) | |||
Linear Mode
