Formula Needed to Omit Characters
 

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


--
addie
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

No sure what is meant by 'recognized'
=LEFT(A1,5)
=RIGHT(A1,2)
=MID(A1,1,5)
=SUBSTITUTE(A1,"AA","--")
Any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"addie" wrote in
message ...

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


--
addie
------------------------------------------------------------------------
addie's Profile:
http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

And you could experiment with Data | Text to Columns
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"addie" wrote in
message ...

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


--
addie
------------------------------------------------------------------------
addie's Profile:
http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

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

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

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

Steve,

Thanks. You were a great help!

Addie


--
addie
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513674

Formula Needed to Omit Characters
 

Glad it was what you needed.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513674