How to Subtitute the Strings in a Range if Length Exceeds

[ygoyal578]

I'm looking for the code where

If Length of any cell exceeds defined max length then string in that cell gets substituted max length from left.
And, if length of cell is within range then skip that cell and go to next cell.

All this is to be done in same cell and column.

Below is the example:

Column A is with string and Column B is Length(just to show). Max length of cell to be set at 5


and output required is below

 

[ygoyal578]

Perhaps I am not understanding but these seem contradictory to me. If you want a formula, it cannot go in the cell the data is already in. So, do you want ..
a) a formula producing the results in another column, or
b) code that replaces the original data with the shortened values?

I am also confused about the right/left. My code reproduced the results you gave as examples in post #1. Are you now saying those samples were incorrect and the values should be truncated at the other end?

Another set of sample data and expected results may help clarify. However, please don't just post an image as we cannot copy from that. Preferred is to use XL2BB but otherwise, just copy from your sheet and paste in the forum like this

Strings	Result
abc.com	abc.c
dedc.com	dedc.
lkjuhygt	lkjuh
juhytfr	juhyt
2lokij	2loki
sdert	sdert
dfgt	dfgt
sgd	sgd
ujhytgfr	ujhyt

I guess I didn't made myself very clear. I want my string to be of max length 58 and that also from right to left.
Below is the just the example where I want strings to be trimmed to 5 characters from right to left.

Strings	Result
abc.com	c.com
sdafadfasdfadsfsd.com	d.com
dsfasdfjsadlfksadf@gmail.com	l.com
sfasdfadsfasd@fdasdf.com	f.com

 

[ygoyal578]

If a string is too short, what character do you want to use to pad it to get the defined length.

Hello, In case string is too short then complete string to be returned.

 

[Peter_SSs]

So, do you want ..
a) a formula producing the results in another column, or
b) code that replaces the original data with the shortened values?

You didn't answer that question so I have assumed b) as I did before.

Sub CutOff_v2()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("right(" & .Address & ",5)")
  End With
End Sub

If you did want a) then this change would put the results in the next column beside the original.

.Value = Evaluate("right(" & .Address & ",5)")
.Offset(, 1).Value = Evaluate("right(" & .Address & ",5)")

 

[mikerickson]

It sounds like =RIGHT(A1, 5) will do what you want.