Costing String

Question

Good Morning, I have a spreadsheet with a list of 12 digit numbers were each segment of the entire number represent a segment of our costing structure.  i.e. First 3 numbers represent department, next 5 represent account and last 4 represent analytical tag.  I have separate worksheets showing which numbers correspond to  v

These numbers need to be converted to their corresponding costing structure item before they are uploaded into our accounting software (they come out of a stand alone payroll).

Has anyone got any ideas on the best way to go about this in excel?  I was thinking along the lines of Vlook up to other worksheets that contain the item numbers and descriptions but not sure how to write the formula so it only looks at certain segments of the string.

Answer 1

Hello,

with the costing string in cell A1, you can extract the first three characters like this:

=left(A1,3)

the next five like this

=mid(A1,4,5)

and the last 4 like this

=right(A1,4)

You can insert these functions into a Vlookup like this:

=vlookup(left(A1,3),DepartmentTable,2,false)

=vlookup(mid(A1,4,5),AccountTable,2,false)

=vlookup(right(A1,4),AnalyticalTable,2,false)

Answer 2

Hi Melanie,

Any updates?

Regards,

Linda

Answer 3

Hi,

Show us an example of your data and the expected result.