Reputation: 65
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.
Upvotes: 0
Views: 236
Reputation: 60494
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
Upvotes: 0
Reputation: 26796
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
Upvotes: 0
Reputation: 59495
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.
Upvotes: 1