Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Left/Mid functions to format download
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Left/Mid functions to format download
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Shams" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Left/Mid functions to format download
Hi John,
Thanks for your reply..let me see if you can help me a little bit mo 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 "John Bundy" wrote: 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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Shams" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using MID/LEFT functions to customize data | Excel Discussion (Misc queries) | |||
Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
=LEFT - Cannot Format | Excel Worksheet Functions | |||
LEFT / RIGHT functions | Excel Worksheet Functions | |||
LEFT, MID functions? | Excel Worksheet Functions |