Formula Needed to Omit Characters

A

addie

Is there a formula that would recognize and omit specific characters
within a field?

Example:

Data Field has the following 9 characters "R0502AA01".

Only the following 7 characters: "R0502--01" should be picked up or
recognized.

What formula will pick up the 1st-5th and the 8th-9th characters and
bypass the 6th & 7th characters?

Addie
 
B

Bernard Liengme

No sure what is meant by 'recognized'
=LEFT(A1,5)
=RIGHT(A1,2)
=MID(A1,1,5)
=SUBSTITUTE(A1,"AA","--")
Any help?
 
S

SteveG

Addie,

As long as your data is always in the same format (9 characters with
the 6th & 7th being the data to omit),

=SUBSTITUTE(A1,MID(A1,6,2),"")

HTH

Steve
 
S

SteveG

Another option, if your numbers are inconsitently formatted where som
have 1 letter in the 6th position, 2 letters in 6th & 7th or no letter
then try,

=CHOOSE(LEN(A1)-6,A1,SUBSTITUTE(A1,MID(A1,6,1),""),SUBSTITUTE(A1,MID(A1,6,2),""))

This only works if the smallest number of characters you'll have is 7.

HTH

Stev
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Altering VLookup Formula 1
Formula suggestions 5
Machine-readable passport calculations 7
Formula to Pick Out Characters within a Text String 7
Points Ranking Formulas 22
duplicate name help please 9
TODAY() issues 6
Formula to look for value in a row depending on unknown value? 2

Top