Macro to strip off suffixes?

[Philip041]

Hello,

I have got a column of data (with header) and have been writing the following formulae in columns B and C to strip off the stuff I don't want;

=FIND(" ",A2, 4)

=IF(ISERROR(B2),A2,LEFT(A2,B2-1))

Then I copy column C as values into D and I've got my values without any stuff after what I want.

How do I get this into a macro without all the extra columns so it just changes the values in column A?

The values come as "AH something" or "AH something possible other things"

Cheers

 

[Andrew Poulsom]

Select your data, choose Edit|Replace, in the What box enter a space followed by *, leave the With box blank and click Replace all. If you record that you will get some VBA code.

 

[Philip041]

It strips off everything after the AH

 

[Andrew Poulsom]

It strips off everything after the AH

I thought that's what you wanted.

 

[Philip041]

Ah, no. I want to get it so it takes off everything after "AH something" all the other somethings that come after I want to remove...

 

[Andrew Poulsom]

If you want a single formula:

=IF(ISERROR(FIND(" ",A2, 4)),A2,LEFT(A2,FIND(" ",A2, 4)-1))

 

[pgc01]

Hi

Using replace. Select the data and

- replace "AH " with "AH"
- replace " *" with "", like Andrew said in post #2
- replace "AH" with "AH "

 

[mikerickson]

You could use TextToColumns and not import the last columns.

 

[Philip041]

Ah, cool. Thanks loads, this is really useful knowledge!