Extract part of a text string

[Martin B]

Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

 

[David Biddulph]

If it's always the same number of characters in each part, then =MID(A1,3,3)&RIGHT(A1,4)
--
David Biddulph

Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

 

[Mike H]

Martin

Maybe this

=MID(A1,FIND("/",A1)+1,3)

Mike

 

[Jim Cone]

Data in B5...
=SUBSTITUTE(MID(B5,FIND("/",B5,1)+1,255),"/","")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Martin B"
wrote in message
Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the /
between
Thanks in anticipation
Martin

 

[Martin B]

Thanks for the quick reponse but if possible I'd like a space between the 3 letters and the 4 digits ie RVI 0444, not RVI0444. This is because I use the result as a look up value in a database which has spaces and also as a direct print out for production who are familier a 3 letter customer code and seperate 4 digit number.
Sorry for not being more specific in my original post

Martin
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message If it's always the same number of characters in each part, then =MID(A1,3,3)&RIGHT(A1,4)
--
David Biddulph

Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

 

[Sandy Mann]

If I understand your requirements try:

=SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("/",A2)),"/","")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

 

[David Biddulph]

=MID(A1,3,3)&" "&RIGHT(A1,4)
--
David Biddulph

Thanks for the quick reponse but if possible I'd like a space between the 3 letters and the 4 digits ie RVI 0444, not RVI0444. This is because I use the result as a look up value in a database which has spaces and also as a direct print out for production who are familier a 3 letter customer code and seperate 4 digit number.
Sorry for not being more specific in my original post

Martin
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message If it's always the same number of characters in each part, then =MID(A1,3,3)&RIGHT(A1,4)
--
David Biddulph

Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

 

[Martin B]

Thanks David, I've played around with some of the other sugestions and got a result.

Have a great 2008
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message If it's always the same number of characters in each part, then =MID(A1,3,3)&RIGHT(A1,4)
--
David Biddulph

Hi
I regularly import data from another program with part numbers identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin