cleanse data, remove letters and extra digits

[cartmanbrra]

Hi,

I have a long list of data and occasionally I'll get a record that looks like this:

AAA#03142-1143412140

I want to extract only "3142"

What is the best way to cleanse my data set to reduce these to 4 digit records?

Thank you

 

[Dim Me as xlNoob]

Is it always in the same format? If so then this formula will pull the laft 4 digits before the "-".

=MID(A1,FIND("-",A1)-4,4)

Or are you looking for a VBA solution to use on a large scale?

 

[FDibbins]

Or, IF the format is always the same...
=mid(A1,6,4)

 

[cartmanbrra]

Thanks, I could use one step further. I'm running this formula right now to pull out specific keywords:

=INDEX(Accounts,MATCH(1,ISNUMBER(SEARCH(Accounts, B1454)),0))

what I want to do is say if this formula above returns #N/A, next look to see if the first 3 letters are AAA, if so then run this formula to pull out the 4 digits, otherwise return a blank.

is that too long of a formula?

 

[FDibbins]

OK, not quite sure what you mean, but lets start with this...
=iferror("this"-formula,if(left(A1,3)="aaa","that"-formula,""))

 

[cartmanbrra]

let me try again. I want to try and do two formulas, in sequence as follows:

1 - =INDEX(Accounts,MATCH(1,ISNUMBER(SEARCH(Accounts, B1454)),0))

If this returns #N/A, next do formula two

2 - =mid(A1,6,4)

The catch is, I only want formula #2 run if the first three letters are AAA, if its anything else, I'd like a blank returned

Maybe this is a VBA thing?

 

[cartmanbrra]

My data looks like this


BLUE345
AAA#03142-1143412140
22 GHD BLUE
7BLUE88

and I want it to look like this, using one formula:

blue
3142
blue
blue

formula #1 above takes care of the word blue, but im stuck getting the number to pull in