| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using MID/LEFT functions to customize data
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.misc
|
|||
|
|||
|
Using MID/LEFT functions to customize data
In b1
=left(A1,find(" ",A1)-1) in C1 =mid(A1,find(" ",A1)+1,len(A1)) "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.misc
|
|||
|
|||
|
Using MID/LEFT functions to customize data
Hi Joel,
Thanks for your reply. I was actually trying to do something a bit different I have my GL#s starting as 50 and Order# starting at 57....my download is in the format that i mentioned below..basically i want to be able to break out the 50's and 57's in 2 columns (i.e. show G/L# and Order# separately) Do let me know if you have any other tips that I can use. "Joel" wrote: In b1 =left(A1,find(" ",A1)-1) in C1 =mid(A1,find(" ",A1)+1,len(A1)) "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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using MID/LEFT functions to customize data
See
http://www.microsoft.com/communities...g=en&cr=US&p=1 -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Shams" wrote: Hi Joel, Thanks for your reply. I was actually trying to do something a bit different I have my GL#s starting as 50 and Order# starting at 57....my download is in the format that i mentioned below..basically i want to be able to break out the 50's and 57's in 2 columns (i.e. show G/L# and Order# separately) Do let me know if you have any other tips that I can use. "Joel" wrote: In b1 =left(A1,find(" ",A1)-1) in C1 =mid(A1,find(" ",A1)+1,len(A1)) "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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using MID/LEFT functions to customize data
try this macro. It copies the data in sheet1 to sheet2 with the results in
sheet2 being two columns. "Shams" wrote: Hi Joel, Thanks for your reply. I was actually trying to do something a bit different I have my GL#s starting as 50 and Order# starting at 57....my download is in the format that i mentioned below..basically i want to be able to break out the 50's and 57's in 2 columns (i.e. show G/L# and Order# separately) Do let me know if you have any other tips that I can use. "Joel" wrote: In b1 =left(A1,find(" ",A1)-1) in C1 =mid(A1,find(" ",A1)+1,len(A1)) "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 | |||
| Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
| How do I customize data labels | Charts and Charting in Excel | |||
| LEFT / RIGHT functions | Excel Worksheet Functions | |||
| LEFT, MID functions? | Excel Worksheet Functions | |||
| Pivot Table Customize functions in the Data Field | Excel Discussion (Misc queries) | |||
Linear Mode
