Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Hi all,
Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Bill,
This works for me... '-------------------------------------------- 'Jim Cone 02/19/2004 Sub FindAlphasOnLeft() Dim lngNum As Long Dim lngLength As Long Dim strExtract As String Dim strCharacters As String strCharacters = Range("F8").Text & CStr(1) lngLength = Len(strCharacters) For lngNum = 1 To lngLength If Mid$(strCharacters, lngNum, 1) Like "#" Then strExtract = Left$(strCharacters, lngNum - 1) Exit For End If Next 'lngNum If Len(strExtract) Then Range("J8").Value = strExtract Else Range("J8").Value = "No Alpha on left side" End If End Sub '-------------------------------------------- Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Hi Jim,
This is what I'm looking for. Thanks. There are two syntax I don't understand. 1. What's the function of CStr(1)? 2. What's the function of Like "#"? Bill -----Original Message----- Bill, This works for me... '-------------------------------------------- 'Jim Cone 02/19/2004 Sub FindAlphasOnLeft() Dim lngNum As Long Dim lngLength As Long Dim strExtract As String Dim strCharacters As String strCharacters = Range("F8").Text & CStr(1) lngLength = Len(strCharacters) For lngNum = 1 To lngLength If Mid$(strCharacters, lngNum, 1) Like "#" Then strExtract = Left$(strCharacters, lngNum - 1) Exit For End If Next 'lngNum If Len(strExtract) Then Range("J8").Value = strExtract Else Range("J8").Value = "No Alpha on left side" End If End Sub '-------------------------------------------- Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Bill,
The cell text might not have any numbers in it. So, the number 1 is added to the end of the text to provide a stop or end point for the loop. "Cstr" converts the number to a string. Cstr is not absolutely necessary in this case as it would have been done automatically, but it does tell you what is going on. The "like" operator is used to compare two strings. The "#" tells it to look for any single digit (0-9). If a number is found, then the code puts all characters to the left of the digit into the strExtract variable and exits the loop. In appears to me that the "like" operator is often overlooked by programmers. It could be worth your time to review the help topic for "like" and try out some of the examples. Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi Jim, This is what I'm looking for. Thanks. There are two syntax I don't understand. 1. What's the function of CStr(1)? 2. What's the function of Like "#"? Bill -----Original Message----- Bill, This works for me... '-------------------------------------------- 'Jim Cone 02/19/2004 Sub FindAlphasOnLeft() Dim lngNum As Long Dim lngLength As Long Dim strExtract As String Dim strCharacters As String strCharacters = Range("F8").Text & CStr(1) lngLength = Len(strCharacters) For lngNum = 1 To lngLength If Mid$(strCharacters, lngNum, 1) Like "#" Then strExtract = Left$(strCharacters, lngNum - 1) Exit For End If Next 'lngNum If Len(strExtract) Then Range("J8").Value = strExtract Else Range("J8").Value = "No Alpha on left side" End If End Sub '-------------------------------------------- Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Hi Jim,
Is the "#" a pre-define function to represent 0-9 in VB? If I want to look for A-Z, which symbol shoud I do? Thanks. -----Original Message----- Bill, The cell text might not have any numbers in it. So, the number 1 is added to the end of the text to provide a stop or end point for the loop. "Cstr" converts the number to a string. Cstr is not absolutely necessary in this case as it would have been done automatically, but it does tell you what is going on. The "like" operator is used to compare two strings. The "#" tells it to look for any single digit (0-9). If a number is found, then the code puts all characters to the left of the digit into the strExtract variable and exits the loop. In appears to me that the "like" operator is often overlooked by programmers. It could be worth your time to review the help topic for "like" and try out some of the examples. Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi Jim, This is what I'm looking for. Thanks. There are two syntax I don't understand. 1. What's the function of CStr(1)? 2. What's the function of Like "#"? Bill -----Original Message----- Bill, This works for me... '-------------------------------------------- 'Jim Cone 02/19/2004 Sub FindAlphasOnLeft() Dim lngNum As Long Dim lngLength As Long Dim strExtract As String Dim strCharacters As String strCharacters = Range("F8").Text & CStr(1) lngLength = Len(strCharacters) For lngNum = 1 To lngLength If Mid$(strCharacters, lngNum, 1) Like "#" Then strExtract = Left$(strCharacters, lngNum - 1) Exit For End If Next 'lngNum If Len(strExtract) Then Range("J8").Value = strExtract Else Range("J8").Value = "No Alpha on left side" End If End Sub '-------------------------------------------- Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Bill,
Is the "#" a pre-define function to represent 0-9 in VB? It is a "pattern character" used by the Like operator. It is not a function. If I want to look for A-Z, which symbol should I do? "[A-Z]" Use it something like the following... Dim strChar as string strChar = "D" If strChar like "[A-Z]" Then... Please look at the help file. Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi Jim, Is the "#" a pre-define function to represent 0-9 in VB? If I want to look for A-Z, which symbol shoud I do? Thanks. -----Original Message----- Bill, The cell text might not have any numbers in it. So, the number 1 is added to the end of the text to provide a stop or end point for the loop. "Cstr" converts the number to a string. Cstr is not absolutely necessary in this case as it would have been done automatically, but it does tell you what is going on. The "like" operator is used to compare two strings. The "#" tells it to look for any single digit (0-9). If a number is found, then the code puts all characters to the left of the digit into the strExtract variable and exits the loop. In appears to me that the "like" operator is often overlooked by programmers. It could be worth your time to review the help topic for "like" and try out some of the examples. Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi Jim, This is what I'm looking for. Thanks. There are two syntax I don't understand. 1. What's the function of CStr(1)? 2. What's the function of Like "#"? Bill -----Original Message----- Bill, This works for me... '-------------------------------------------- 'Jim Cone 02/19/2004 Sub FindAlphasOnLeft() Dim lngNum As Long Dim lngLength As Long Dim strExtract As String Dim strCharacters As String strCharacters = Range("F8").Text & CStr(1) lngLength = Len(strCharacters) For lngNum = 1 To lngLength If Mid$(strCharacters, lngNum, 1) Like "#" Then strExtract = Left$(strCharacters, lngNum - 1) Exit For End If Next 'lngNum If Len(strExtract) Then Range("J8").Value = strExtract Else Range("J8").Value = "No Alpha on left side" End If End Sub '-------------------------------------------- Regards, Jim Cone San Francisco, CA "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract alphabet from string.
Hi Bill
This will pull out all of the alphanumeric characters from the first one to the last - e.g. ab145 gives ab145 $@ab145 gives ab145 ab$@145 gives ab $@ab$@145 gives ab you can hack the alphanum function to allow / dis-allow whatever characters that you want.... HTH David ############################################ Function alphanum(letter As String) Select Case (letter) Case "a" To "z", "A" To "Z", 0 To 9: alphanum = True Case Else: alphanum = False End Select End Function Sub macro1() Dim x, y As Integer Dim tocheck, result As String Dim done, doing, started As Boolean x = 1 y = 1 done = False doing = True started = False While (Range("A" & x).Value < "") y = 1 result = "" tocheck = Range("A" & x).Value While ((y <= Len(tocheck)) And (Not (done))) If (alphanum(Mid(tocheck, y, 1))) Then result = result & Mid(tocheck, y, 1) started = True Else If (started) Then doing = False End If If (Not (doing)) Then done = True y = y + 1 Wend doing = True done = False started = False Range("b" & x).Value = result x = x + 1 Wend End Sub ############################################ "Bill Choy" wrote in message ... Hi all, Are there any suggestion to extract the first few alphabet from a string? For example, "RC125", I want to scan this string from the first character and extract "RC" to the other cell. Please suggest a method to recognize between A- Z and 0-9. Thanks. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract characters from Right of string | Excel Worksheet Functions | |||
EXTRACT NUMBER FROM STRING | Charts and Charting in Excel | |||
Extract from string | Excel Discussion (Misc queries) | |||
extract date from string | Excel Worksheet Functions | |||
Extract sub string | Excel Worksheet Functions |