If the cell begins with certain words remove the words

[snapajap]

I am preparing my bank statements for tax time. I need them sorted by the business I transacted with, but my bank often titles the transactions with "Checkcard 0123" here's an example
CHECKCARD 0108 LUMBER LIQUIDATORS 1352 TORRANCE CA
I would like it to read

LUMBER LIQUIDATORS 1352 TORRANCE CA 2443

Sometimes the cells do not contain the "checkcard" word and doesn't need modifying, I have about 2000 rows to be sorted in column B.

Any help would be awesome. If it makes any difference, I am using Open Office

Thanks a bunch!

 

[Gary McMaster]

Hello, and welcome to the board.

Perhaps overly simplistic but a simple search and replace might work.

Replace all instances "Checkcard 0108 " with "" (note the trailing space)

or

Replace all instances "Checkcard ???? " with "" (note the trailing space & wild card characters)

Gary

 

[snapajap]

Hello, and welcome to the board.

Perhaps overly simplistic but a simple search and replace might work.

Replace all instances "Checkcard 0108 " with "" (note the trailing space)

or

Replace all instances "Checkcard ???? " with "" (note the trailing space & wild card characters)

Gary

Thanks Gary! How would I be able to apply that to all of column B? Sorry, newbie here

 

[Gary McMaster]

I've never used open office but in Excel you could select all of column B by clicking on the column header (the letter "B" at the top of the column) then execute the find & replace. It would only perform the operation on the selection.

If the text to be replaced only appears in column B you wouldn't have to select anything. Just tell it to find and replace all instances. Of course it would only do column "B" because that's the only place it would find a match.

Gary

 

[snapajap]

OK, got it with the exception of the "wildcard". I tried ???? and ****, any suggestions?
I can't thank you enough

 

[Gary McMaster]

Maybe open office uses different characters for wild cards? I can't answer that one for you.

A very tedious way to do it without wild cards would be something like:

Replace all instances "Checkcard 0109 " with "Checkcard 010 " (note the trailing space)
Replace all instances "Checkcard 0108 " with "Checkcard 010 " (note the trailing space)
Replace all instances "Checkcard 0107 " with "Checkcard 010 " (note the trailing space)
...

Replace all instances "Checkcard 0100 " with "Checkcard 010 " (note the trailing space)

Then redo the above procedure to get rid of the 3rd digit

Then redo the above procedure to get rid of the 2nd digit etc

Tedious to say the least. If the digits are all four places and unique it will take at 40 passes just to get rid of the numbers.

You will also have to be careful not to accidentally replace the street numbers and zip codes etc. That is the reason for the trailing space.

It may still be easier than editing 2,000 entries by hand.

Gary

 

[snapajap]

The problem is that the 4 digits following the Checkcard are random, so I'd like to Replace "Checkcard 0984" with ""
Could that be done like Replace "Checkcard ****" with ""
Sorry to make this drag on.

 

[Gary McMaster]

Try google "Wildcards Open Office" I looked quickly and saw that Open Office does support wildcards. They're just different from Excel.

The tedious technique I described in post number 6 may still work with random numbers though it may take a lot more than 40 iterations

 

[snapajap]

Try google "Wildcards Open Office" I looked quickly and saw that Open Office does support wildcards. They're just different from Excel.

The tedious technique I described in post number 6 may still work with random numbers though it may take a lot more than 40 iterations

It looks like I should man up and get Excel for mac. Thanks for all of your help!