creating a Left Function using a macro

[Legacy 93538]

Hi

I need to create a Left forumla which is created within a macro and placed in cell I2 and autofilled to the variable number z + 1.

The left forumla needs to add together the value in cell J2 and the value K2. However i need to cut the value in J2 so the result is only 7 characters long.

I think its should look like the following but I think this take the character off the end of the string instead of the first.

PPCWBSht.Range("J2").forumla = "=Left(J2 & K2,7)"

Can anyone help me?

Thanks

Jessicaseymour[]

 

[saltkev]

Hi

I may have have got a little confused as to your requirements, however does this help.

' option 1 Alpha Chars

Sheets("sheet1").Range("b2").Formula = "=Left(concatenate(J2 , K2),7)"

'option 2 Numeric Chars

Sheets("sheet1").Range("b3").Formula = "=Left(sum(J3 + K3),7)"

regards

 

[Norie]

Jessica

Do you only want the first 7 characters of the result of joining the 2 strings?

If you do the code/formula you posted should do that.

If you don't can you explain what you do want, perhaps with some examples.

 

[Legacy 93538]

Hi

Sorry if i didn't explain very well, i am not very good and explaning.

What it is, is that i have one workbook with two columns, J and K. The J column has AAAA in all the rows and the K column has a list starting at A.1 and ending at A.1035.

In the I column i need a forumla which adds together the values in the two columns but whenever the charatcter is more than 7 characters it removes the first chacater of the J column value.

Does that help?

Thanks for replying by the way!!

 

[Comfy]

"=Left(J2,7) & K2"

If I read your question correctly you want the first 7 characters of J2 plus K2?

 

[Comfy]

=IF(LEN(J2)>7,Left(J2,7) & K2, J2 & K2)

If you want the last seven characters change Left to Right

 

[Legacy 93538]

Hi

Thanks for everyones assistance.

"=Left(J2,7) & K2"

This works very well

Thank you sooo much for your help!!!

Thanks

Jeskit

 

[Comfy]

Use this If statement, you might get an error if the Cell contains a string less than seven characters long