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

--
Regards,
Tom Ogilvy

"adiacc" wrote:

> 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

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).

Tom Ogilvy wrote:
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "adiacc" wrote:
>
> > 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

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!!!!!!!!!!!!



"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "adiacc" wrote:
>
> > 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

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:F406E781-78A7-41EA-95D2-(E-Mail Removed)...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "adiacc" wrote:
>
>> 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

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?



"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:F406E781-78A7-41EA-95D2-(E-Mail Removed)...
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "adiacc" wrote:
> >
> >> 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
>
>
>