Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with strings
Here is one way to do it (by using user defined
functions). It can also be done using strictly VBA macros. It was not clear where and how the data comes into the worksheet. I've made the assumption it's in a column (A) in a worksheet. By using the two user defined functions below, one in column B, the other in column C, the desired result is extracted from the raw data. Function FindAccount(txt As String) 'find the account number in the text Dim i As Integer Dim j As Integer Dim AcctStart As Integer Dim AcctEnd As Integer For i = 1 To Len(txt) If IsNumeric(Mid(txt, i, 1)) Then AcctStart = i Exit For End If Next i For j = AcctStart To Len(txt) If Mid(txt, j, 1) = " " Then AcctEnd = j - 1 Exit For End If Next j FindAccount = Mid(txt, AcctStart, (AcctEnd - AcctStart)) End Function Function FindAccountName(txt As String) 'find the account name in the text Dim i As Integer Dim j As Integer Dim AcctStart As Integer Dim AcctEnd As Integer For i = 1 To Len(txt) If IsNumeric(Mid(txt, i, 1)) Then AcctStart = i Exit For End If Next i For j = AcctStart To Len(txt) If Mid(txt, j, 1) = " " Then AcctEnd = j - 1 Exit For End If Next j FindAccountName = Mid(txt, (AcctEnd + 2), Len(txt) - (AcctEnd + 1)) End Function Hope the above was of use. Andrew Lenczycki -----Original Message----- I am using VBA in Excel XP with Windows 2000. I have a series of variable length strings in the following format: [Some Text] [account number] [some more text] in actuality, the above pattern may appear as: xltzy tmv olw 9908472 Concentration Account (USD) okplk rtes 007552642366 Pension Account (USD) lmv 65654237 Retirement Account (USD) Each of the three component pieces (text and account numbers) above are variable in length, including the digits in the account number (which is all values). I need an efficient method to extract the account number and the last bit of text. Results for the above example should look like: Column 1 Column 2 9908472 Concentration Account 007552642366 Pension Account 65654237 Retirement Account Do I need to loop through each character of each string to find the account or is there a better way? Your help and example code would be most appreciated...thanks in advance. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Working with Strings | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
last name, first name strings | New Users to Excel | |||
How can I count strings within strings | Excel Worksheet Functions |