Extracting Letters

[kenpcli]

How do I extract a letter from a group of numbers and put in into a different cell. For example: Cell B6 has A123456 and I want the letter A taken and put into cell A6 and leave the rest of the number in B, if B doesnt have a letter in it, then leave the A cell blank and color it yellow.

Thanks

 

[kenpcli]

This is what I have, but it says "expected end of statement" when it gets to the "A"

Range("A6").Formula = "=IF(LEFT(B6,1)="A","A",IF(LEFT(B6,1)="b","B", ""))"

 

[Fluff]

How about

   If Not IsNumeric(Left(Range("B6"), 1)) Then
      Range("A6") = Left(Range("B6"), 1)
      Range("B6") = Mid(Range("B6"), 2)
   Else
      Range("A6").Interior.Color = vbYellow
   End If

 

[Rick Rothstein]

Here is an alternate that you can consider...

[table="width: 500"]
[tr]
	[td]  If Range("B6") Like "#*" Then
    Range("A6").Interior.Color = vbYellow
  Else
    Range("A6:B6") = Split(Application.Replace(Range("B6"), 2, 0, " "))
  End If[/td]
[/tr]
[/table]

 

[kenpcli]

Thank you

 

[kenpcli]

What if I need it to look down column B until there is no data?

 

[Fluff]

Like this

   Dim Cl As Range
   For Each Cl In Range("B6", Range("B" & Rows.Count).End(xlUp))
      If Not IsNumeric(Left(Cl, 1)) Then
         Cl.Offset(, -1) = Left(Cl, 1)
         Cl = Mid(Cl, 2)
      Else
         Cl.Offset(, -1).Interior.Color = vbYellow
      End If
   Next Cl

 

[kenpcli]

I am trying to fit it between these two lines of code, but it doesnt work.

[COLOR=#ff0000] End With[/COLOR]
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
   Dim Cl As Range
   For Each Cl In Range("B6", Range("B" & Rows.Count).End(xlUp))
      If Not IsNumeric(Left(Cl, 1)) Then
         Cl.Offset(, -1) = Left(Cl, 1)
         Cl = Mid(Cl, 2)
      Else
         Cl.Offset(, -1).Interior.Color = vbYellow
      End If
   Next Cl
   
[COLOR=#ff0000]    Range("D515").Select[/COLOR]

 

[Fluff]

In what way "Doesn't it work"?
Also when posting code, please use code tags, the # icon in the reply window

 

[kenpcli]

Sorry, this does work. I will include the # sign next time.