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

1. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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