+ Reply to Thread
Results 1 to 9 of 9

How to use the "Left Function" on Pivot Table Column

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    NY, NY
    MS-Off Ver
    Windows 10
    Posts
    14

    Question How to use the "Left Function" on Pivot Table Column

    Greetings

    Please see the snip of the existing table.

    MyPivotTable.JPG

    My problem is simple. I want to use the Left function on the three columns that begin with the five-digit number and description; the goal is to extract the 5-digits only and show in the pivot table (minus the description.)

    Of course, I can use the left function in the source table and create another column (or three), and use that "new column in the pivot table.

    However, I already have 300+ columns derived from SQL, so I do not want to create any additional columns.

    Seems, I am having much trouble with this approach; and thinking it may be impossible.

    Your assistance is greatly appreciated. Thank You.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to use the "Left Function" on Pivot Table Column

    You could use code to simply overwrite those cells with the first 5 digits, but it would be far simpler to amend your source query.
    Rory

  3. #3
    Registered User
    Join Date
    06-22-2020
    Location
    NY, NY
    MS-Off Ver
    Windows 10
    Posts
    14

    Re: How to use the "Left Function" on Pivot Table Column

    Rorya

    I am using the vba code below.

    Please Login or Register  to view this content.

    Basically I am reading the pivot table range extents into an array "arr" exactly as the data appears at the pivot table.

    A possible solution would be to utilize the "left function" to the three columns before it gets into the array.

    I will admit that is more difficult presently for me and suggestions are most welcomed, thanks.
    Last edited by AliGW; 07-12-2020 at 03:31 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: How to use the "Left Function" on Pivot Table Column

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    06-22-2020
    Location
    NY, NY
    MS-Off Ver
    Windows 10
    Posts
    14

    Re: How to use the "Left Function" on Pivot Table Column

    AliGW

    Thank you for the correction to the code I included, and I will remember for next time. So perhaps we can begin with how I believe my problem can be solved.

    I have narrowed it down to just before filling the 2-d array "arr" from an already filtered pivot table.

    In this pivot table are three columns of text; all similar to this arrangement - "61050 - Admin Gen Purpose". Obviously there are 1,000's of these category codes.

    I need to test for the name of these three columns, when True, extract the first five digits of the string - then use that to fill the array.

    Here is the code (lets hope I get these code-tags correct)

    Please Login or Register  to view this content.
    Well, I added my sample code "between" the code tags, so we'll see how it formats; looks terrible presently.

    In any event, I believe just after the "For y = LBound(arr, 2) To UBound(arr, 2)" is where I should add the additional code.

    Your guidance is most appreciated. Thank You.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to use the "Left Function" on Pivot Table Column

    Since they all appear to use the same format, you could test that using Like:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-22-2020
    Location
    NY, NY
    MS-Off Ver
    Windows 10
    Posts
    14

    Re: How to use the "Left Function" on Pivot Table Column

    Thanks rorya - I will try this now and let all know.

  8. #8
    Registered User
    Join Date
    06-22-2020
    Location
    NY, NY
    MS-Off Ver
    Windows 10
    Posts
    14

    Re: How to use the "Left Function" on Pivot Table Column

    Rorya - this is a brilliant and elegant solution.

    I was initially going to attempt a test the columns y = 1, 2, or 3 but your suggestion [cellValue Like "##### -*"] is much more efficient.

    I appreciate your expertise and thank you.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to use the "Left Function" on Pivot Table Column

    Glad to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"
    By skydivetom in forum Excel Charting & Pivots
    Replies: 26
    Last Post: 06-27-2020, 11:03 AM
  2. Replies: 7
    Last Post: 06-27-2020, 05:02 AM
  3. Replies: 7
    Last Post: 03-06-2019, 05:01 PM
  4. Pivot Table query regarding "SUMPRODUCT" vs function "SUM" on pivot
    By sfyris.c in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-10-2015, 07:42 AM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. Applying a "Left" formula to an entire column with heading "ValueCol"
    By aad401 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 04:14 PM
  7. Replies: 2
    Last Post: 07-11-2007, 06:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1