Leave only letters in range of cells vba

[ThomasOES]

Hello

How to leave only letters (element symbols) in a range of cells. The range begins at E1. Each element (Ex. Al240 %) is one cell.

Make this:

Al240%

As189%

B208%

C165%

Ca393%

Co340%

Cr267%

Cu327%

Fe249%

Mn_Calc.

Mo386%

Nb400%

Ni341%

P177%

Pb220%

S180%

Sb206%

Si_Calc.

Sn326%

Ta362%

Ti337%

V411%

W400%

Zr360%

<tbody>
</tbody>

Look like this:

Al

As

B

Be

C

Ca

Co

Cr

Cu

Fe

La

Mg

Mn

Mo

N!

Nb

Ni

P

Pb

S

Sb

Si

Sn

Ta

Ti

V

W

Zn

Zr

<tbody>
</tbody>

Also remove any spaces and the phrase "Calc".

Thanks for any help

Tom

 

[ThomasOES]

Please disregard the N! in the second line

 

[Special-K99]

Prob a better way but this seems to work

Sub k1()
lastcol = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
For i = 5 To lastcol
Cells(2, i) = Left(Cells(1, i), 2)
Next
End Sub

 

[Fluff]

Another option

Sub GetElements()
   With Range("E1", Cells(1, Columns.Count).End(xlToLeft))
      .Value = Evaluate(Replace("if(isnumber(mid(@,2,1)*1),left(@,1),left(@,2))", "@", .Address))
   End With
End Sub

 

[ThomasOES]

I tried this and it works great to remove the phrase and %. How to .Replace What:=numbers, Replacement:=""

Range("E1", Selection.End(xlToRight)).Select
For Each cell In Selection
With cell
    .Replace What:="_Calc.", Replacement:=""
    .Replace What:=" %", Replacement:=""
End With
Next

Tom

 

[ThomasOES]

Thanks MVP. Perfect

Tom

 

[Fluff]

Glad we could help & thanks for the feedback