Re: how to remove unwanted text in a cell
No it shouldn't because the IsNumeric is testing a partial string which is extracted from the cell so it's not concerned about the format.
Here is the order I would suggest near the top of the code:
Code
'the first 4 digits will either represent a number or will be "blank"
If Trim(Left(cCell.Value, 4)) <> vbNullString Then
'make sure it's a number in case the code is run again
If Not IsNumeric(Trim(Left(cCell.Value, 4))) Then GoTo NextCell
'set the cell format to text
cCell.NumberFormat = "@"
'some rows appear not to have a ; character at the end so need to allow for this
cCell.Value = Trim(Mid(cCell.Value, j + 1, IIf(InStr(cCell.Value, ";") = 0, Len(cCell.Value), _
InStr(cCell.Value, ";") - j - 1)))