Insert <tab> in a phrase with condition

Hi Good morning!

I have a table of contents!

eg:
Mr.Jesus P Perez 995 656 55 4554 949 4
Ms.KirstenC Marrero E 49 8 4 6 4 67 655 7
Mrs.Faye W Smith 9 69 6F5 74 8675 7 46 6
Mr.Herbert V Johnson 55 6 4 4 674 6 47 4 4787
Mr.Daniel J Thomoson 79A 48F 8 4 7668 8 6

Result :

Mr.Jesus P Perez<tab> 995 656 55 4554 949 4
Ms.KirstenC Marrero<tab> E 49 8 4 6 4 67 655 7
Mrs.Faye W Smith<tab> 9 69 6F5 74 8675 7 46 6
Mr.Herbert V Johnson<tab> 55 6 4 4 674 6 47 4 4787
Mr.Daniel J Thomoson<tab> 79A 48F 8 4 7668 8 6

Suggestion: Insert a <tab> or <any special character> after the 4th word which starts with CAPS (regardless its followed by a Alphabet or Numeric).

Appreciate your early reply!

Thank you

why not just this? Seems a lot simpler.

Please Login or Register  to view this content.

Excel doesn't seem to like having a tab in a cell so the CHAR(9) might need to be chnaged to " " if you just want a larger space.

Thank you and appreciate your early reply!

I found a prob at (Sl.No: 2) :

2 Ms.KirstenC Marrero E 49 8 4 6 4 67 655 7 Ms.KirstenC Marrero E<tab> 49 8 4 6 4 67 655 7

It was suppose to add a <tab> after "Marrero" which is the 4th word (sorry for not have a space Between "KirstenC" but actually it has to be treated as 2 words. <tab> should appear before CAP "E"


Thank you for the affort!... await your reply

Originally Posted by Crooza

why not just this? Seems a lot simpler.

Please Login or Register  to view this content.

Excel doesn't seem to like having a tab in a cell so the CHAR(9) might need to be chnaged to " " if you just want a larger space.

Thanks for the reply!
Appreciate your effort


It should not be counted with spaces but with CAPS (4th caps), as the Second record :
2 Ms.KirstenC Marrero E 49 8 4 6 4 67 655 7 Ms.KirstenC Marrero E<tab> 49 8 4 6 4 67 655 7

It was suppose to add a delimiter after "Marrero" which is the 4th word (sorry for not have a space Between "KirstenC" but actually it has to be treated as 2 words) conversion error. delimiter should appear before CAP "E"

Originally Posted by sktneer

Or this.....

Assuming your string is in A1, then

Please Login or Register  to view this content.

This will add a ":" at the desired place in the string.

Thanks a lot!

I know the complex formulas above are trying to address your capital letter issue but my formula should work for the problem you posted above unless I'm missing something. Seems you just want to pick up the third space in each example

Please Login or Register  to view this content.

I've changed to using a colon for this example

Originally Posted by kjxavier

Thanks for the reply!
Appreciate your effort


It should not be counted with spaces but with CAPS (4th caps), as the Second record :
2 Ms.KirstenC Marrero E 49 8 4 6 4 67 655 7 Ms.KirstenC Marrero E<tab> 49 8 4 6 4 67 655 7

It was suppose to add a delimiter after "Marrero" which is the 4th word (sorry for not have a space Between "KirstenC" but actually it has to be treated as 2 words) conversion error. delimiter should appear before CAP "E"

Originally Posted by Crooza

I know the complex formulas above are trying to address your capital letter issue but my formula should work for the problem you posted above unless I'm missing something. Seems you just want to pick up the third space in each example

Please Login or Register  to view this content.

I've changed to using a colon for this example

Ok I see your problem now. ;-)