Removing Numbers from a cell

[nsaracco1]

Hello, In cell E2 I have SESAME NJ 219211550. I am trying to leave the SESAME NJ in cell F2. Any suggestions?
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 284"]
<tbody>[TR]
[TD="class: xl64, width: 284"]SESAME NJ 219211550 [/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[sandy666]

if this is representative example you can try PowerQuery(Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"raw", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})
in
    #"Extracted Text Before Delimiter"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]raw[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SESAME NJ 219211550[/td][td][/td][td=bgcolor:#E2EFDA]SESAME NJ[/td][/tr]
[/table]

 

[James006]

Hello,

You can use an Array Formula

[FONT=inherit]=[/FONT][URL="https://exceljet.net/excel-functions/excel-textjoin-function"][FONT=inherit]TEXTJOIN[/FONT][/URL][FONT=inherit]([/FONT][FONT=inherit]""[/FONT][FONT=inherit],[/FONT][FONT=inherit]TRUE[/FONT][FONT=inherit],[/FONT][URL="https://exceljet.net/excel-functions/excel-if-function"][FONT=inherit]IF[/FONT][/URL][FONT=inherit]([/FONT][URL="https://exceljet.net/excel-functions/excel-iserr-function"][FONT=inherit]ISERR[/FONT][/URL][FONT=inherit]([/FONT][COLOR=#0062A0][FONT=inherit][URL="https://exceljet.net/excel-functions/excel-mid-function"]MID[/URL](E2[/FONT][/COLOR][FONT=inherit],[/FONT][URL="https://exceljet.net/excel-functions/excel-row-function"][FONT=inherit]ROW[/FONT][/URL][FONT=inherit]([/FONT][URL="https://exceljet.net/excel-functions/excel-indirect-function"][FONT=inherit]INDIRECT[/FONT][/URL][FONT=inherit]([/FONT][FONT=inherit]"1:100"[/FONT][FONT=inherit])),[/FONT][FONT=inherit]1[/FONT][FONT=inherit])[/FONT][FONT=inherit]+[/FONT][FONT=inherit]0[/FONT][FONT=inherit]),[/FONT][COLOR=#0062A0][FONT=inherit][URL="https://exceljet.net/excel-functions/excel-mid-function"]MID[/URL](E2[/FONT][/COLOR][FONT=inherit],[/FONT][URL="https://exceljet.net/excel-functions/excel-row-function"][FONT=inherit]ROW[/FONT][/URL][FONT=inherit]([/FONT][URL="https://exceljet.net/excel-functions/excel-indirect-function"][FONT=inherit]INDIRECT[/FONT][/URL][FONT=inherit]([/FONT][FONT=inherit]"1:100"[/FONT][FONT=inherit])),[/FONT][FONT=inherit]1[/FONT][FONT=inherit]),[/FONT][FONT=inherit]""[/FONT][FONT=inherit]))[/FONT]

Hope this will help

 

[AlKey]

Or this

Book1
	E	F
2	SESAME NJ 219211550	SESAME NJ
Sheet1

Cell Formulas
Range	Formula
F2	=TRIM(LEFT(SUBSTITUTE(E2," ",REPT(" ",50)),100))

 

[Rick Rothstein]

Or this

Book1
	E	F
2	SESAME NJ 219211550	SESAME NJ
Sheet1

Cell Formulas
Range	Formula
F2	=TRIM(LEFT(SUBSTITUTE(E2," ",REPT(" ",50)),100))

Your formula will work one and two name cities only... unfortunately, New Jersey has a few three-name cities in it (South Bound Brook, West New York, etc.).

 

[jtakw]

Hi,

A couple of Non-array formula options, normally entered:

Book1
	E	F	G
2	SESAME NJ 219211550	SESAME NJ	SESAME NJ
3	South Bound Brook NJ 219211551	South Bound Brook NJ	South Bound Brook NJ
4	West New York NJ 219211552	West New York NJ	West New York NJ
Sheet647

Cell Formulas
Range	Formula
F2	=TRIM(SUBSTITUTE(E2,TRIM(RIGHT(SUBSTITUTE(E2," ",REPT(" ",99)),99)),""))
G2	=LEFT(E2,MIN(FIND({0,1,2,3,4}+{0;5},E2&1/17))-2)