change a value on a string of data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi my 1st post so I hope I make sense:
I have strings of data like the EG below
000000000000 0000 000 Andrea xxxx 222 3333
000000000000 0000 000 Louise xxxx 222 3333
So what I need to do is change the names to ***** the names can be different
but the field is always the same number of characters and in the same
location in the line
The file is a TXT file.

Any idea?
Thank you
Andrea
 
Sub AABB()
For Each Cell In Range("A1:A10")
s = Cell.Value
iloc = InStr(23, s, " ", vbTextCompare)
s1 = Left(s, 22)
s3 = Right(s, Len(s) - iloc)
s2 = "***** "
Cell.Value = s1 & s2 & s3
Next
End Sub
 
For Tom's code to work you're going to need to read the text file into
a worksheet (each line into a new cell), then run Tom's code, then
write the cells back into the text file.. The code for that should be
pretty easy to find on the newsgroup here (just do a search for it).
 
Thank you that is great!!
one more question.....sorry..
so in the below example I need to do the replace after 23 caracters when the
line starts with 1
and after 29 when the line starts with a 2

how would I do that?
100000000000 0000 000 Andrea xxxx 222 3333
200000000000 0000 000 xxxxx Louise 222 3333

Thank you!!!!!!!!!!!!
 
For Each Cell In Range("A1:A10")
s = Cell.Value
if left(s,1) = "1" then
i = 23
else
i = 29
end if
iloc = InStr(i, s, " ", vbTextCompare)
s1 = Left(s, i-1)
s3 = Right(s, Len(s) - iloc)
s2 = "***** "
Cell.Value = s1 & s2 & s3
Next
 
Thank you!!

One problem when I convert back the file from Excel to .txt some of the
lines (not all)
Have a " as the 1st caracter in the line.

That happens only in the .TXt file as if i look in the excel file I can not
see the "

Could you please help?
 
Back
Top