Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Uppercase Letters | Excel Programming | |||
Extract Capital Letters | Excel Discussion (Misc queries) | |||
Extract two letters | Excel Discussion (Misc queries) | |||
Extract letters from a cell | Excel Worksheet Functions | |||
How will I do Excel user-defined function to extract letters from string | Excel Programming |