Extract first four letters from string....

[J.W. Aldridge]

Hi,

I have a list of codes in column A. I need to extract the first four
letters in every row (disregaurding any numbers or spaces) and paste
them in the same adjacent row in column C.

I also need to do the same for column B to D.

CODE EXTRACTED VALUE
00APPLES APPL
APPLE APPL
APPLE APPL
12313APPL APPL
.....APPLES APPL



Thanx

 

[Jim Thomlinson]

Here is a simple udf that will return the position of the first non-numeric
character in a string. Put it in a standard code module and use it like this
in a cell.

=mid(a1, firstletter(a1), 1)

Public Function FirstLetter(ByVal InputString As String) As Integer
Dim lngCounter As Long
Dim lngStringLength As Long
Dim lngReturnValue As Long

lngReturnValue = -1
lngStringLength = Len(InputString)

For lngCounter = 1 To lngStringLength
If Not IsNumeric(Mid(InputString, lngCounter, 1)) Then
lngReturnValue = lngCounter
Exit For
End If
Next lngCounter

FirstLetter = lngReturnValue
End Function

 

[Brad]

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")," ",""),4)

 

[Don Guillett]

one way
Sub fourletters()
For Each c In Selection
For i = 1 To Len(c)
If Mid(c, i, 1) Like "[A-z]" Then
sc = i
Exit For
End If
Next i
c.Offset(, 1) = Mid(c, sc, 4)
Next c
End Sub

 

[Rick Rothstein \(MVP - VB\)]

You can use this worksheet formula to extract the 4 consecutive characters
starting with the first letter character in A1...

=MID(A1,MIN(FIND(CHAR(ROW($65:$90)),A1&"abcdefghijklmnopqrstuvwxyz")),4)

This formula can be copied down.

Rick

 

[Lars-Åke Aspelin]

As FIND is case sensitive I guess the formula has to be expanded a bit
to take care of this. Maybe like this:

=MID(A1,MIN(FIND(CHAR(ROW($65:$90)),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ");FIND(CHAR(ROW($97:$122)),A1&"abcdefghijklmnopqrstuvwxyz")),4)

If you know that there are only upper case letter (or lower case
letters) the formula can be reduced, but the solution presented is a
mix of upper case ($65:$90) and lower case ("abcd...xyz") letters.

Or am I missing something here?

Lars-Åke

 

[Rick Rothstein \(MVP - VB\)]

Thanks for catching the problem with my first posting. Here is a simpler
fix...

=MID(A1,MIN(FIND(CHAR(ROW($97:$122)),LOWER(A1)&"abcdefghijklmnopqrstuvwxyz")),4)

Rick

 

[J.W. Aldridge]

Thanx all....

(Had to find a big enough spoon to eat all of that info, then had to
figure out how to digest it.....

Ok, I want to keep my workbook functioning in a timely matter (have
several pages of info to perform this extraction) so I think I want to
avoid formulas here.

Looks like Don's code would be the easiest or closest to what I am
looking for but didn't quite get it to work.

Getting an error on this part here.

c.Offset(, 1) = Mid(c, sc, 4)


Any recommendations?

Need to extract Column A to C, then B to D.

Thanx all,

When I get my first million, I'm buying you all Krystal Burgers!

 

[Rick Rothstein \(MVP - VB\)]

Ok, I want to keep my workbook functioning in a timely matter (have

several pages of info to perform this extraction) so I think I want to
avoid formulas here.

Give this macro a try then (it processes both Column's A and B within the
same run)...

Sub GetFirstFourLetters()
Dim Col As Variant
Dim X As Long
Dim Z As Long
Dim LastRow As Long
With Worksheets("Sheet8")
For Each Col In Array("A", "B")
LastRow = .Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To Len(.Cells(X, Col).Value)
With .Cells(X, Col)
If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then
.Offset(0, 2).Value = Mid$(.Value, Z, 4)
Exit For
End If
End With
Next
Next
Next
End With
End Sub

Rick

 

[J.W. Aldridge]

PURR-FECTO!

Thanx.


When you see my name in lights.... Remind me about that Krystal
burger.
(You can make that a double, with cheese!)