Replacing text

[percyth1]

I have thousands of reference codes just like below and I need a formula that changes the code from this:

ENW000000159794
ENW000000159834
ENW000000159862
ENW0000135968-E
ENW0000135968-G
ENW0000139386-E

To this:

159794
159834
159862
135968
135968
139386

Basically removing all the ENW's and 0's in front as well as any "-E" or "-"G"'s on the end.

Can anyone help?

[vlady]

hello

are you using 2010?

try using the DATA - TEXT to COLUMN
use first the 0 as delimeter then 2nd time use the -

hope helps.
by the way it will create a different column 2 or 3 columns ..

[percyth1]

hello

are you using 2010?

try using the DATA - TEXT to COLUMN
use first the 0 as delimeter then 2nd time use the -

hope helps.
by the way it will create a different column 2 or 3 columns ..

I'm using 2007, but thanks, i'm willing to give anything a try right now.

[percyth1]

I have got it to remove the ENW by using the formula "=RIGHT(A2,LEN(A2)-3)" so I need another formula that will just remove a -G or -E from the left hand side. Then hopefully excel will recognise it as a number and automatically format out all the 0's.

[vlady]

you'll have some problems if you use that formula, cutting off the characters will not solve that.
attached is a sample picture for you.

[scottylad2]

The characters still to be removed, will there always be -G or -E? Can there be any other? If not then its fairly simple to remove them and by adding a +0 to the end it will convert it to numeric and should indeed lose the leading 0's

The formula will be almost identical to the one you've used at the start, replace Right function with Left and replace the -3 with -2 and at the extreme end of the formula do a + o to convert to numeric

[johnrichie]

=RIGHT(A1,LEN(A1)-FIND("1",A1)+1)

Try this.. Still figuring out how the 'E-'s & 'G-'s are to be removed

[vlady]

Still in eradicating "es & Gs" ??

try this
C1 = contains numbers with -E & - G

=LEFT(C1,LEN(C1)-FIND("-",C1)+4)

[scottylad2]

Single cell solution

=IF(ISNUMBER(SEARCH("-",RIGHT(A1,LEN(A1)-3))),LEFT(RIGHT(A1,LEN(A1)-3),LEN(RIGHT(A1,LEN(A1)-3))-2),RIGHT(A1,LEN(A1)-3))+0

[Alf]

Another singel cell solution

=IF(MID(A1,LEN(A1)-1,1)="-",MID(A1,4,LEN(A1)-5)*1,MID(A1,4,LEN(A1)-3)*1)

Alf

[percyth1]

Incredible!

I managed to figure it out with a 6 column solution but the single column solution is great!

Cheers

[percyth1]

Ok how do I mark this thread as solved?

[johnrichie]

Another singel cell solution

=IF(MID(A1,LEN(A1)-1,1)="-",MID(A1,4,LEN(A1)-5)*1,MID(A1,4,LEN(A1)-3)*1)

Alf

Dear Alf,

Please could you explain your formula?

Thanks

[johnrichie]

Dear Alf,

Please could you explain your formula?

Thanks

Thanks Alf.. figured it out

[Alf]

Hi percyth1

Ok how do I mark this thread as solved?

Like this

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

and a word of warning never use your mail adress in an open forum unless you wish to recive loads of spam in your mail box.

Hi johnrichie

I guess it was the *1 that you did'n figure out at once. Was a bit unsure myself as the OP never said if the result should be a string or a number. So I took a chance thinking a number would be ok.

Alf