Formula Needed to Omit Characters

[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

[Bernard Liengme]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bernard Liengme]

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

"addie" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

[SteveG]

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,MID(A1,6,2),""))

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

HTH

Steve

[addie]

Steve,

Thanks. You were a great help!

Addie

[SteveG]

Glad it was what you needed.

Steve