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

 

[jasonb75]

Just use =LEFT(A2,5)

Anything shorter than 5 characters will just show whatever is in the cell.

 

[Peter_SSs]

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

Try this with a copy of your data.

Sub CutOff()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9))
End Sub

 

[ygoyal578]

Just use =LEFT(A2,5)

Anything shorter than 5 characters will just show whatever is in the cell.

Hello Jason, Actually I need the macro code for doing it.

 

[Peter_SSs]

Hello Jason, Actually I need the macro code for doing it.

So did the suggested macro do what you wanted?
(BTW, if you want a specific method - eg macro - it would save people wasted effort if that was stated clearly in your first post. )

 

[jasonb75]

if you want a specific method

In fairness to the OP, they did ask for code, I missed the line that you quoted in your earlier reply

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

 

[Peter_SSs]

In fairness to the OP, they did ask for code, I missed the line that you quoted in your earlier reply

Good point - I missed that line. Apologies @ygoyal578

 

[ygoyal578]

So did the suggested macro do what you wanted?
(BTW, if you want a specific method - eg macro - it would save people wasted effort if that was stated clearly in your first post. )

He Peter, Thanks for the help, I have tried the code and was running fine but I want the dynamic formula where length of string remains to 58 and if length is greater than 58 then extra length should be substituted from left to right. Currently it substituting from right to left.

Or we can also have the code where it by default make the string for defined length from right to left

 

[Peter_SSs]

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

but I want the dynamic formula

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

 

[mikerickson]

....
Or we can also have the code where it by default make the string for defined length from right to left

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