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
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Hi..,
Is there a formula to enable me to copy just the numbers of a cell which also contains letters/characters (example Cell A1: FT’’/@12345’ ) All I want to do is remove all the non number characters (leaving me with 12345 in the above example)..? I’m unable to use the left, mid and right formulas (well be longwinded) as there is no specific number of characters before or after the actual numbers (which are always together/not split up)…? Any thoughts ..? Many thanks Monk |
#2
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
"Monk" wrote: Hi.., Is there a formula to enable me to copy just the numbers of a cell which also contains characters (example Cell A1: ''12345' ) All I want to do is remove all the '' characters (leaving me with 12345 in the above example)..? I’m unable to use the left, mid and right formulas (well be longwinded) as there is no specific number of '''' characters before or after the actual numbers (which are always together/not split up)…? Any thoughts ..? Many thanks Monk |
#3
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
If the number will only be at the right end of the string, try this:
A1: (some string ending with numbers) B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),99)*1 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Monk" wrote: Hi.., Is there a formula to enable me to copy just the numbers of a cell which also contains letters/characters (example Cell A1: FT’’/@12345’ ) All I want to do is remove all the non number characters (leaving me with 12345 in the above example)..? I’m unable to use the left, mid and right formulas (well be longwinded) as there is no specific number of characters before or after the actual numbers (which are always together/not split up)…? Any thoughts ..? Many thanks Monk |
#4
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Hello, I attempted to edit the first post, but it has posted a new thread.. To clear up the situation.. It appears the only characters mixed within the numbers is the ‘character which sometimes appears at the beginning and end of the set of numbers (but not consistent). So just need to remove the ‘’’’ characters.. ? I have tried your method Ron (with thanks), but as you mentioned it only works if there is no ‘ characters to the right of the numbers, which unfortunately there is on some of the entries. Monk..! |
#5
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
OK...see if this works:
A1: (some string containing consequtive numbers) B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Monk" wrote: Hello, I attempted to edit the first post, but it has posted a new thread.. To clear up the situation.. It appears the only characters mixed within the numbers is the ‘character which sometimes appears at the beginning and end of the set of numbers (but not consistent). So just need to remove the ‘’’’ characters.. ? I have tried your method Ron (with thanks), but as you mentioned it only works if there is no ‘ characters to the right of the numbers, which unfortunately there is on some of the entries. Monk..! |
#6
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
On Mon, 23 Jan 2006 09:17:02 -0800, "Monk"
wrote: Hi.., Is there a formula to enable me to copy just the numbers of a cell which also contains letters/characters (example Cell A1: FT’’/@12345’ ) All I want to do is remove all the non number characters (leaving me with 12345 in the above example)..? I’m unable to use the left, mid and right formulas (well be longwinded) as there is no specific number of characters before or after the actual numbers (which are always together/not split up)…? Any thoughts ..? Many thanks Monk For strings up to 255 characters: 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. Use this Regular Expression formula: =REGEX.SUBSTITUTE(A1,"[^0-9]") --ron |
#7
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Not a formula, but a macro.
Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben MS Excel MVP On Mon, 23 Jan 2006 09:17:02 -0800, "Monk" wrote: Hi.., Is there a formula to enable me to copy just the numbers of a cell which also contains letters/characters (example Cell A1: FT’’/@12345’ ) All I want to do is remove all the non number characters (leaving me with 12345 in the above example)..? I’m unable to use the left, mid and right formulas (well be longwinded) as there is no specific number of characters before or after the actual numbers (which are always together/not split up)…? Any thoughts ..? Many thanks Monk |
#8
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Don't know how often you need to do this but you can copy the column and paste into WORD and then do a FIND and REPLACE - Just do a FIND any letter, in WORD this is, ^$. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=504099 |
#9
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Thanks to all who took time to reply..
Ron, your formual works brill.. Thanks again ... Monk * "Ron Coderre" wrote: OK...see if this works: A1: (some string containing consequtive numbers) B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Monk" wrote: Hello, I attempted to edit the first post, but it has posted a new thread.. To clear up the situation.. It appears the only characters mixed within the numbers is the ‘character which sometimes appears at the beginning and end of the set of numbers (but not consistent). So just need to remove the ‘’’’ characters.. ? I have tried your method Ron (with thanks), but as you mentioned it only works if there is no ‘ characters to the right of the numbers, which unfortunately there is on some of the entries. Monk..! |
#10
|
|||
|
|||
Removing characters from a cell (keeping only the numbers)
Here's a shorter formula for extracting consecutive numbers from anywhere in
a string: A1: (string containing consecutive numbers, eg abc123xyz) B1:=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01 23456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1) ),1)))) It replaces my previously posted: LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"012 3456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) *********** Regards, Ron XL2002, WinXP-Pro "Monk" wrote: Thanks to all who took time to reply.. Ron, your formual works brill.. Thanks again ... Monk * "Ron Coderre" wrote: OK...see if this works: A1: (some string containing consequtive numbers) B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Monk" wrote: Hello, I attempted to edit the first post, but it has posted a new thread.. To clear up the situation.. It appears the only characters mixed within the numbers is the ‘character which sometimes appears at the beginning and end of the set of numbers (but not consistent). So just need to remove the ‘’’’ characters.. ? I have tried your method Ron (with thanks), but as you mentioned it only works if there is no ‘ characters to the right of the numbers, which unfortunately there is on some of the entries. Monk..! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
copying cell names | Al | General Discussion | 3 | August 11th, 2005 03:01 PM |
removing some of the characters from a cell | Patience | General Discussion | 2 | May 3rd, 2005 08:28 PM |
if cell starts with characters formula | Norman Kong via OfficeKB.com | General Discussion | 3 | March 24th, 2005 10:18 AM |
Counts all cell containing even numbers in the range | Thomas | Worksheet Functions | 5 | July 9th, 2004 11:51 AM |
Is 255 characters in a cell the max? | Mike_S | Setting up and Configuration | 2 | January 24th, 2004 12:43 AM |