-2

I have a column of product numbers that are all formatted like this:

MK444LLA

...same number and letter pattern, same character count. I need to insert a / into each cell so they all end up like this: 

MK444LL/A

I'm thinking I just need a solution for the first row, which I can then apply to the entire column.

Community
  • 1
  • 1
christopherhlee
  • 89
  • 2
  • 4
  • 13

1 Answers1

2

Use the Left and Right string functions and concatenate the three parts together with &.

Left(Range("A1").Text, 7) & "/" & Right(Range("A1").Text, 1)

Left(Range("A1").Text, 7) - this returns the first seven characters.

Right(Range("A1").Text, 1) - this returns the last character.

Tamás Sengel
  • 55,884
  • 29
  • 169
  • 223
  • My resulting string comes out in an odd format. "MK-01-1/34" – christopherhlee Jun 05 '18 at 16:37
  • @chlee218 in your question you said all rows had the same character count but the example you mentioned in that comment is a different length than the example from your question.... – Marcucciboy2 Jun 05 '18 at 17:17
  • 2
    @chlee218 if instead each row can have different size then use `Left(Range("A1").Text, Len(Range("A1").Text) - 1) & "/" & Right(Range("A1").Text, 1)` – Marcucciboy2 Jun 05 '18 at 17:18
  • @Marcucciboy2 all of the rows do have the same character count. But, the resulting string comes out in that odd format (and the number after the "/" continues to climb with each row). Could this be because I used a R1C1 formula to arrive at the initial string? – christopherhlee Jun 05 '18 at 17:27
  • 1
    @chlee218 it must be, because the answer above will only stick the / character in what test already exists. – Marcucciboy2 Jun 05 '18 at 17:29