Splitting part codes

MattB · Mar 22nd, 2002, 05:15 AM

Hi all, a simple one for somebody!
Column A shows 100's of part codes much like the one shown:
1HAT60847BC-LB16SA
I want column B to show LB16SA.
Any bright ideas?
Thanks

Matt

Matt · Mar 22nd, 2002, 05:33 AM

Try this formula in B1

=right(A1,6) and then copy formula down

Aladin Akyurek · Mar 22nd, 2002, 07:39 AM

Quote:

On 2002-03-22 04:15, MattB wrote:
Hi all, a simple one for somebody!
Column A shows 100's of part codes much like the one shown:
1HAT60847BC-LB16SA
I want column B to show LB16SA.
Any bright ideas?
Thanks

Matt

In case the segment you want to extract is of variable length, use:

=RIGHT(A1,LEN(A1)-SEARCH("-",A1))

Dave Hawley · Mar 22nd, 2002, 08:05 AM

Also..

=REPLACE(A3,1,FIND("-",A3),"")

Matt · Mar 22nd, 2002, 08:09 AM

Or you could use text to columns on the data menu

- select delimited as your data type
- for your delimiter select other and enter "-"

this will also split your cells as requested

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 05:15 AM	#1
MattB Board Regular Join Date: Mar 2002 Posts: 62	Hi all, a simple one for somebody! Column A shows 100's of part codes much like the one shown: 1HAT60847BC-LB16SA I want column B to show LB16SA. Any bright ideas? Thanks Matt

Mar 22nd, 2002, 05:33 AM	#2
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Try this formula in B1 =right(A1,6) and then copy formula down

Mar 22nd, 2002, 08:05 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Also.. =REPLACE(A3,1,FIND("-",A3),"")

Mar 22nd, 2002, 08:09 AM	#5
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Or you could use text to columns on the data menu - select delimited as your data type - for your delimiter select other and enter "-" this will also split your cells as requested