Remove Text, Keep Numbers in a cell

[leon6782]

Hello,

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

 

[AlphaFrog]

Try this. Again, this is a CSE formula.

=SUM(IF(ISERROR(VALUE(LEFT(A1:A5,1))),0,IF(ISNUMBER(VALUE(LEFT(A1:A5,2))),VALUE(LEFT(A1:A5,2)),VALUE(LEFT(A1:A5,1)))))

This will ignore any cell value where the 1st character is not a number. It will sum the cells where the 1st one or two characters are numbers.

 

[pgc01]

Hi

Another option, also entered with CSE:

=SUM(0+(0&IF(LEFT(A1:A5)<="9",LEFT(A1:A5)&IF(MID(A1:A5,2,1)<="9",MID(A1:A5,2,1),""),"")))

... assuming the numbers have 1 or 2 digits and other possible characters are letters, as in the examples.

 

[AlphaFrog]

Here's another formula. This is NOT as CSE formula...

=SUMPRODUCT(--(0&LEFT(A1:A5,ISNUMBER(LEFT(A1:A5,1)*1)+ISNUMBER(Left(A1:A5,2)*1)))*1)

 

[pgc01]

Here's another formula. This is NOT as CSE formula...

=SUMPRODUCT(--(0&LEFT(A1:A5,ISNUMBER(LEFT(A1:A5,1)*1)+ISNUMBER(Left(A1:A5,2)*1)))*1)

Nice one, AlphaFrog.

 

[AlphaFrog]

Thanks. It was your 0& trick that gave me the inspiration.

 

[dracron]

Hello,

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

Copy and paste this.



if you need the col to be specific . Change ActiveSheet.UsedRange to (and this is an example) Range ("h:h")






Sub LiamObvSux()


With CreateObject("VBScript.RegExp")


.Global = True


.Pattern = "[^a-z]"



For Each LookIn In ActiveSheet.UsedRange


For Each Match In .Execute(LookIn)


LookIn.Replace What:=Match, Replacement:=""


Next


Next


End With


End Sub