Extract first four letters from string....
 

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

Extract first four letters from string....
 

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
--
HTH...

Jim Thomlinson


"J.W. Aldridge" wrote:

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

Extract first four letters from string....
 

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(D4,0,""),1,""),2,""),3, ""),4,""),5,""),6,""),7,""),8,""),9,"")," ",""),4)
--
Wag more, bark less


"J.W. Aldridge" wrote:

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

Extract first four letters from string....
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
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

Extract first four letters from string....
 

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&"abcdefghij klmnopqrstuvwxyz")),4)

This formula can be copied down.

Rick


"J.W. Aldridge" wrote in message
...
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

Extract first four letters from string....
 

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&"ABCDEFGHIJ KLMNOPQRSTUVWXYZ");FIND(CHAR(ROW($97:$122)),A1&"ab cdefghijklmnopqrstuvwxyz")),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


On Tue, 22 Jul 2008 12:08:52 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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&"abcdefghi jklmnopqrstuvwxyz")),4)

This formula can be copied down.

Rick


"J.W. Aldridge" wrote in message
...
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

Extract first four letters from string....
 

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

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

Rick

"Lars-Åke Aspelin" wrote in message
...

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&"ABCDEFGHIJ KLMNOPQRSTUVWXYZ");FIND(CHAR(ROW($97:$122)),A1&"ab cdefghijklmnopqrstuvwxyz")),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


On Tue, 22 Jul 2008 12:08:52 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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&"abcdefgh ijklmnopqrstuvwxyz")),4)

This formula can be copied down.

Rick


"J.W. Aldridge" wrote in message
...
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

Extract first four letters from string....
 

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!

Extract first four letters from string....
 

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

Extract first four letters from string....
 

PURR-FECTO!

Thanx.


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