Combined Right with Left Formula problems

1. Combined Right with Left Formula problems

Hi all,

Got one that I've been trying to work out.

Going to be simple but I still cant seen to work it out.

I have a string of data:

1,100,315.00ZuAl2,336.00PW100.00

And what I need to extract is the 100 after the PW but the string can also be

1,100,315.00ZuAl2,336.00PW80.00

The 80 or 100 can be any number, but will alway be a 2 or a 3 digit number

I've tried the =Right(A1, Len(a1)-24)

Bu tthis brings back the .00 after the numbers.

Ive tried combining left and mid but with no luck.

Any help?

Thanks

2. Re: Combined Right with Left Formula problems

Using those strings could you not use:

=0+REPLACE(A1,1,FIND("PW",A1)+1,"")

If PW always appears at pos 25 then replace the FIND with the constant (26).

3. Re: Combined Right with Left Formula problems

Hey DO,

Thanks for that mate.

I;ve tried to replace the FIND with 26

0+REPLACE(A1,1,(26)("PW",A1)+1,"")

But it doesn't like it.

Is there any way to do this without referencing to the "PW" expression?

.

4. Re: Combined Right with Left Formula problems

If the 25-26 is constant then you should find that

=0+REPLACE(A1,1,26,"")

works ok (at least it does on the samples)

5. Re: Combined Right with Left Formula problems

Ah it was my syntax.

Cheers.

You'll probably shoot me for this, but I was trying to figure this out to transpose it as an expression into access.

And I've just realised that the replace is valid.

If there is no other way I will start a new thred in the access forum and you can close this one.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1