Excel Cell Parsing

Question

I have two columns of about 4,000 cells of data each. I need to format each column in a similar way.

Column 1: Scenerio 1: Each cell has data that begins with "qwe". If there is a "bc" after the "qwe", I need to trim off all 5 characters.

Scenerio 2: Each cell has data that begins with "qwe". If there is NOT a "bc" after the "qwe", I need to trim off ONLY the first 3 characters ("qwe") and the final character of the data string. (This would be the last character at LENGTH of the data string)

Column 2: Scenerio: I need to trim off the first three characters of each string in each cell.

For the first column, I have absolutely no idea how to check if the 4th and 5th characters are "bc" and then branch into the appropriate action.

For the second column, I was just going to call MID(CELL,3,LENGTH_OF_STRING_IN_CURRENT_CELL). However, I cant figure out how to call the length of the data in the cell from within a function.

All help is appreciated. I don't know anything about VB macros and I only (obviously) know a little about Excel functions.

Answer 1

Assuming Column1 is A and Column2 is B, please try:

=IF(AND(LEFT(A1,3)="qwe",MID(A1,4,2)="bc"),RIGHT(A1,3),RIGHT(A1,LEN(A1)-3))  

and

=MID(B1,4,LEN(B1)-3)  

copied down to suit.

Answer 2

Assuming you really want to use VBA to do this instead of spreadsheet functions like pnuts suggests:

You can call worksheet functions using Application.WorksheetFunction in VBA; however, I don't think simple string functions are available from there; generally you'll find the more complex functions that work across rows & columns.

Still, BASIC has lots of good string functions, and if you obtain a cell's value as a string you can directly use those functions for string manipulation. Here's a simple example.

Set cell = ActiveSheet.Range("B2")
Dim val As String
Let val = cell.Value
Let part = Mid(val, 4, 2)

Then you can write the cell value back if you like:

Let cell.Value = part

Answer 3

The MID function, if given a length parameter that is longer than the string, will just return the balance of the string. So for some of the functions, it's OK to just put a big number there, instead of computing it. I used 99, but if some of your strings are larger, just make that number large enough.

For column 1:

C1: =IF(LEFT(A1,5)="qwebc",MID(A1,6,99),MID(A1,4,LEN(A1)-4))

For column 2:

D1: =MID(B1,4,99)

and fill down