# Help with text data

#### gl45

##### Board Regular
I would like to loop through text data in column e3:e200
and truncate the small c at the right end of the data.
The data is alphanumeric, and also contains some empty cells.
OC4000c or ABCDE10000CL1\$3Xc
the result should read OC4000 OR ABCDE10000CL1\$3X
Thanks

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try

Code:
``````Sub Noc()
Dim i As Integer
For i = 3 To 200
With Range("E" & i)
If .Value <> "" Then .Value = Left(.Value, Len(.Value) - 1)
End With
Next i
End Sub``````

In F3 =IF(RIGHT(E3,1)="c",LEFT(E3,LEN(E3)-1),IF(LEN(E3)=0,"",E3). Copy down the formula to row 200 on column F, select F3:F200 ->Copy, go to E3, right-click -> Paste Special... -> Values -> OK; delete the formulas in column F.

Mr. VO
Thanks for the macro, but there is a glitch.

ABD3500N2Lc the result after the macro is run is correct as ABD3500N2L

the macro also does truncate the last character even if there is no c at the end
I only need the small c to be truncate if does appear in the text data.
Thanks

MR, gegs
your "if" statement does work , but I was looking for a way to avoid the copy and paste process every time I download the data.
Thank you.

Try

Code:
``````Sub Noc()
Dim i As Integer
For i = 3 To 200
With Range("E" & i)
If .Value <> "" Then
If Right(.Value, 1) = "c" Then .Value = Left(.Value, Len(.Value) - 1)
End If
End With
Next i
End Sub``````

Then adjust VoG's macro with my formula
Code:
``````Sub Noc()
Dim i As Integer
For i = 3 To 200
With Range("E" & i)
If .Value <> "" Then
If Right(.Value,1) = "c" Then
.Value = Left(.Value, Len(.Value) - 1)
End If
End If
End With
Next i
End Sub``````

Thank you gents both macro did work fine,

Replies
0
Views
123
Replies
7
Views
288
Replies
3
Views
408
Replies
10
Views
418
Replies
3
Views
216

1,203,123
Messages
6,053,638
Members
444,675
Latest member
FedElecQaEng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back