Select only a certain chararcter count

Scaiveau

New Member
Joined
Apr 12, 2011
Messages
3
I need to copy a column, with 30 pLus chatarters in a column of no more than 30 Characrters.
When I go into Data validation, I can select my new colum to read only 30 charaters (so I match my importing format) but when I copy my 30+ characters column in my no more than 30 character, I get notified that my text is too long for each entry. I do not want to re-enter several 1000's of entries. I want my column to read the 1st 30 charatarter and leave behind the left over.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

You can use the =LEFT() function to return the first 30 characters from a string.

For example,
Code:
=LEFT(a1,30)

If you want to also show the remaining characters, you can use
Code:
=MID(a1,31,255)
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi there,

Welcome to the board.

Maybe try this:

Code:
Sub test()
    lRow = Range("A" & Application.Rows.Count).End(xlUp).Row
    For i = 2 To lRow
        Cells(i, 2).Value = Left(Cells(i, 1).Value, 30)
    Next i
End Sub

Change ranges to suit.
 

Scaiveau

New Member
Joined
Apr 12, 2011
Messages
3
Thank you Gerald


Hi, welcome to the board.

You can use the =LEFT() function to return the first 30 characters from a string.

For example,
Code:
=LEFT(a1,30)

If you want to also show the remaining characters, you can use
Code:
=MID(a1,31,255)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top