Create Fixed Character (Length)

M

MARKG

Board Regular
Joined
Jul 3, 2002
Messages
56
I'm trying to clean up a variable length column in Excel in order to import into Monarch so I can properly trap the data.

If I have variable data in column A with # characters ranging from 1 through 40 characters, what code would create a new column where every cell had 50 characters (using spaces as filler)?

For example in cells A1 and A2 is the text:

Mark (4 characters)
Mark's Excel Problem (20 characters)

I want spaces inserted for total character count of 50 characters (the period represents a space for visual representation on this post):
Mark..............................................(4 characters plus 46 spaces = 50)
Mark's Excel Problem.........................(20 characters plus 30 spaces = 50)

thank-you
Mark
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Try

Code: 
Sub test()
Dim LR As Long, i As Long, txt As String * 50
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("B").Insert
For i = 1 To LR
    txt = Range("A" & i).Value
    Range("B" & i).Value = txt
Next i
Columns("B").AutoFit
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
609
diversification
D
F
  • Question
Concatenating Strings of texts with set limits to # of characters, and need spaces if characters do not reach limit
Replies
6
Views
1K
Anthony47
A
C
  • Question
Cell Time Format > Backspace before first character with no spaces present, changes cell data appearance
Replies
3
Views
968
Chopsaki
C
M
  • Question
Aircraft Takeoff Data
Replies
5
Views
328
mikeba
M
J
  • Question
Scraping data from WIFI networks that are available
9 10 11
Replies
109
Views
5K
Prome3
P

Forum statistics

Threads
1,214,947
Messages
6,122,413
Members
449,082
Latest member
tish101

Share this page

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
Back
Top