Using Left/Mid functions to format download

[Guest]

Folks,
The following is a sample of how the data is downloaded from SAP into Excel:

Column A

50000 Free Goods Tax
57001 Free Goods Tax from SD
50023 Billboard Production
50052 Consumer Sampling
57002 Miscellaneous Expenses
50023 Billboard Production
50024 Sampling
57003 FTC 2008


Basically Column A displays both G/L Account and Order # where G/L is
represented first with no leading spaces and Order # is represented with 3
leading spaces (e.g. 57001)

I am trying to take the Order # and show it in another column where for
example, 57002 and 57003 can be shown in the following way:

Column A Column B
57002 Miscellaneous Expenses 50023 Billboard
Production
57002 Miscellaneous Expenses 50052 Consumer
Sampling
57003 FTC 2008 50023
Billboard Production
57003 FTC 2008 50024
Sampling

What I tried to do was insert a column to the left of the existing column
and then do a formula as: (if(left(b2,1)=" ",mid(b2,3,5),left(b2,5))

All this is doing is literally lining up the G/L and Order # in the new
column..I am looking for a way where I am only getting the Order # in the
first column and the G/L Description in the 2nd column. In my data
arrangment the same G/L can repeat itself across different Order #

Short of actually deleting rows..what could be a good approach to achieving
my data representation? I will appreciate any pointers. Hopefully, my
example is clear enough. Thanks.

Regards,
Shams.

 

[Guest]

Depends on your data really, if all of the column G stuf started with 50 you
could look at the left two and put it in column C if =50, then in column C is
blank then B=A, that is about the only way you could do it. BTW you can use
=trim(A1) to take the extra spaces out. In my example column B
=IF(C1="",Trim(A)1,"")
and column C
=IF(LEFT(A1,2)="50",Trim(A1),"")

hope that helps

 

[Guest]

Hi John,
Thanks for your reply..let me see if you can help me a little bit more:

when i download the data I get G/L # and Order# showing up in the same
column as I described below...so, both the 50's (G/L#) and the 57's (Order#)
show up in the same column...my goal is to be able to break them out in 2
columns where any one G/L may have multiple Order #s or any one Order# may
have multiple G/L #s attached to them.i.e. show them side by side...

I don't have one column only starting with 50 but the consistent pattern is
50 and 57 all showing up in one column only distinguished by the leading
spaces before the 57's.

Do let me know if you have any more nuggest for me! Thanks