VBA to Cut and Paste Based on Character Count

vba_student

New Member
Joined
Dec 20, 2016
Messages
2
Hi All,

First time poster, but a huge fan of the forum and have received tremendous assistance from past posts in my work - I don't know where I would be without the help from this community.

I am in need of a simple VBA code to execute cut and paste based on the number of characters in a cell and repeat for each row.

The Data appears as follows:

P Q R
12345678 Calculate Account

More specifically, based on my requirements in the above data:
1. Count the number of characters on column P
2. If it's less than or equal to 8 characters THEN;
3. Cut and paste the contents in column P and Q over to the right one cell.
4. Repeat for each row

In the end, I'd like the Data to appear as follows:

P Q R
12345678 Calculate Account

Some caveats:
1. The data is in text format (not sure if this will have an impact).
2. The data is in a formatted table, which I already see won't take the function - LEN(text).

Many Thanks in advance!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
hi vba_student, welcome to the forum. Excel considers a space as a character, does your table have strings separated by spaces in column P and will that affect your 8 character count? Also, is the table a named table as in a Tables collection, or just an organized area of the worksheet with borders? If it is an Excel table (part of collection) then what is the table name? It makes a difference in how the vba code is written to address the cell locations.
 
Last edited:

vba_student

New Member
Joined
Dec 20, 2016
Messages
2
Hi JLGWhiz,

Thanks for your response! To answer your question, there are no spaces in column P that would affect the character count - I doubled checked to ensure there are no space before or after the value. And regarding the table, it is a tables collection with name, "Table_ATV_QUERIES.accdb20".

My before and after data view did not come out as I intended in my initial post, so I'm going to try it in a different format.

Before:

Cell P2 = 12345678
Cell Q2 = Calculate Account
Cell R2 = Blank

After:

Cell P2 = Blank (since there are less than or equal to 8 characters - Cut and paste "12345678 to Q2)
Cell Q2 = 12345678 (Paste from P2)
Cell R2 = Calculate Account (Paste from Q2)

So, basically since Cell P2 has 8 characters, the contents in P2 and Q2 shift one cell to the right, leaving P2 blank. And I would like to repeat this for every row in my table.

Thanks!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OK, I am goint to have to pass on this one because my expertise with the ListObjects(Tables) is almost zero. Maybe somebody with more knowledge in that area will pick up on this thread. Give them a few hours and if no response, click 'Reply' and type 'Bump' in the reply box and then post it. after a couple of Bumps, if you don't get a reply, then start a new thread and include the additional information that you added above about the tables, name, location, size, etc. Aslo, at the bottom of this post, you will see the last word: Attachements. Click on that to get info on how to post an image of your worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top