Results 1 to 2 of 2

Thread: Formula for CF that will look at ONLY the first 6 characters in a string
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    71
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for CF that will look at ONLY the first 6 characters in a string

    Hi,

    My code I currently have...

    Code:
    =IF(OFFSET(A8,0,1)=OFFSET(A8,-1,1),OFFSET(A8,-1,0),OFFSET(A8,-1,0)+1)
    Basically if the value in the cell next to it is the same as the one above then the value of the cell the formula is in, it'll be the same number correlating to the one above... Makes it easier for me to run a conditional format for unique ID's. Now, this is great and all but I ran into a barrier... I'd like for this formula to ONLY read the first 6 characters in the string. They will always go...

    X = Letter
    - = -
    1 = Number

    XXX-11

    Reason why I need it to read the first 6 characters is because I'm going to concatenate two cells together to make it like XXX-11 X. I just need it to read the first string of 6 characters to tell me if it's similar.

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for CF that will look at ONLY the first 6 characters in a string

    @MFish

    A bit of a longwinded CF formula but maybe...

    Code:
    =AND(CODE(MID(G1,1,1))>=65,CODE(MID(G1,1,1))<=90,CODE(MID(G1,2,1))>=65,CODE(MID(G1,2,1))<=90,CODE(MID(G1,3,1))>=65,CODE(MID(G1,3,1))<=90,MID(G1,4,1)="-",CODE(MID(G1,5,1))>=48,CODE(MID(G1,5,1))<=57,CODE(MID(G1,5,1))>=48,CODE(MID(G1,5,1))<=57)
    Excel 2010
    G
    1XXX-11fgrt543
    2ABC-94
    3X2XX-11fgrt545
    4123- 23
    5XYZ-63

    Sheet C




    Hope that helps.
    Last edited by Snakehips; Jun 5th, 2019 at 05:11 AM.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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