Share this content
3

Excel - another question....

Excel to SAGE

Didn't find your answer?

have been asked to transfer XERO to SAGE data

XERO pl + sl  do not have any defaul t account 'A/C' field - as required by SAGE just the account name

so export from xero contact details

so in excel thinking was...

insert new column[A/c}    then copy Name to A/C [Account name] .......then within excel shorten the A/C field....it is this which I  am wondering how to effect  eg     from XERO  NAME =  PETER SMITH INDUSTIRES PTY, into csv in excel- then   copy this  to a new column =  A/c and shorten to PETERSMI

now hopefully 2 different fields to upload on to SAGE via the   template uploader

OR

is there another way/ better way plse

and yes it has to be xero>sage!!!

many thanks

 

Replies (3)

Please login or register to join the discussion.

avatar
By WhichTyler
14th Feb 2020 14:54

If I have understood you correctly, you could use the LEFT() function to strip out the first x characters of the long name

But watch out as they need to be unique, so check for Peter Smith Technologies coming out the same as Peter Smallwood Accountancy if you only use the first 7 characters

Thanks (1)
avatar
By daniel_
15th Feb 2020 17:00

=LEFT(UPPER(SUBSTITUTE(TRIM(A1)," ","")),8)

will take the first 8 characters from A1 after removing any spaces.

Thanks (2)
avatar
By Yoyo29
16th Feb 2020 19:34

You could create an alphanumeric coding whereby the account code consists of the first x letters of the supplier/customer name (or the first x letters of the 2nd word in company names stating with "The"), followed by a numeric number sequence such as 0001, 0002, 0003 etc.

For example, if the accounts codes comprises of the 1st 4 characters then 4 numbers, the account codes would be:
PETE0001 for Peter Smith Technology Ltd
PETE0002 for Peter Johns Ltd
ROYA0001 for The Royal Society of Medicine
ROYA0002 for The Royal Academy of Engineering

I'd suggest first sorting the account names in alphabetical order.
Then follow WhichTyler's suggestion of using the LEFT() function to extract the first x characters for the alpha part of the account code.
In another column, use the COUNTIF() function to identify where you have more than one instance of the alpha section of the code.
Use a third column to create the full Alphanumberic codes.

Thanks (1)
Share this content