Help Required!

[choccy]

Hi,

I am currently helping someone with an innovatory spreadsheet which is to my horror nearly 15,000 lines long!

Anyhow,

One of the many products codes, are based on the sizes, so for example, i will have a sequence of product codes like this in two columns - one is our code with sizes and the second column is the suppliers code but with no sizes

SASC00001-16 SCN0001-16
SASC00001-18 SCN0001-18
SASC00001-20 SCN0001-20
SASC00001-22 SCN0001-22
SASC00001-24 SCN0001-24
SASC00001-28 SCN0001-28
SASC00002-07 SCN0002-07
SASC00002-16 SCN0002-16
SASC00002-18 SCN0001-18
SASC00002-20 SCN0001-20
SASC00002-22 SCN0001-22
SASC00002-24 SCN0001-24
SASC00002-28 SCN0001-28
SASC00003-07 SCN0003
SASC00003-08 SCN0003
SASC00003-18 SCN0003
SASC00003-20 SCN0003
SASC00003-22 SCN0003
SASC00003-24 SCN0003
SASC00003-28 SCN0003
SASC00004-07 SCN0004
SASC00004-08 SCN0004
SASC00004-18 SCN0004
SASC00004-20 SCN0004
SASC00004-22 SCN0004
SASC00004-24 SCN0004
SASC00004-28 SCN0004
SASC00005-07 SCN0005
SASC00005-08 SCN0005
SASC00005-18 SCN0005
SASC00005-20 SCN0005
SASC00005-22 SCN0005



I basically need to be able to add the last two numbers to each of the product numbers in the second column to match those in the first column.

Is there a way for me to use a formula to help me as there are hundreds like this to complete!

 

[footoo]

In C1 : =B1 & RIGHT(A1,3)
Fill down to the end
Copy column C and PasteSpecial/Values
Delete column B

 

[choccy]

In C1 : =B1 & RIGHT(A1,3)
Fill down to the end
Copy column C and PasteSpecial/Values
Delete column B

Hi,

Thanks for that.

My column B is in use so it would need to go into column A

Sorry for not being clearer, is there a small change to that formula so it will go into C?

Thanks

 

[footoo]

I don't understand - what columns contain the data?
Just amend the formula accordingly.

 

[choccy]

I don't understand - what columns contain the data?
Just amend the formula accordingly.

Hi

Sorry

Ok,

So column A is where the data is....

SASC00001-16

Not every field though has a -07 or -16 - some are just SASC00001 (for example) so there will be some where no additional add on number is required.

The formula will be in Column C as Column B is already in use.

So Ideally, i need the formula (now i know can be done) to allow for the SASC00001-16 or SASC00001 and it will be in Column C

 

[footoo]

=IF(ISNUMBER(SEARCH("-",A1)),B1&RIGHT(A1,3),B1)

 

[choccy]

=IF(ISNUMBER(SEARCH("-",A1)),B1&RIGHT(A1,3),B1)

Thank You, Perfect!

 

[Fluff]

For future reference while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Formula Required to add on Numbers as required to other data

Hi, I am currently helping someone with an innovatory spreadsheet which is to my horror nearly 15,000 lines long! Anyhow, One of the many products codes, are based on the sizes, so for example, i will have a sequence of product codes like this SASC00001-16 SCN0001-16 SASC00001-18...

www.excelforum.com

 

[shuqairi]

Try using this Formula in beside column

=MID(A1,SEARCH("-",A1,1)+1,2)

 

[choccy]

For future reference while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Formula Required to add on Numbers as required to other data

Hi, I am currently helping someone with an innovatory spreadsheet which is to my horror nearly 15,000 lines long! Anyhow, One of the many products codes, are based on the sizes, so for example, i will have a sequence of product codes like this SASC00001-16 SCN0001-16 SASC00001-18...

www.excelforum.com

Hi

Many thanks everyone.

Apologises for the cross posting!