+ Reply to Thread
Results 1 to 5 of 5

Combined Right with Left Formula problems

  1. #1
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    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. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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. #3
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    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. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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. #5
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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