2
0
I have single column that needs to be split to multiple, like Text-to-columns in excel. However there is a small challenge. Conventional delimiter will not work. Consider the string below
Original: Domain\Domain Admins Domain2\User Group Domain3\Developers .....(And so on)
Required: Domain\Domain Admins | Domain2\User Group | Domain3\Developers .....(And so on)
The pipe in the required string means that it needs to be split here and copied to next column as per the length of the string.
I have the list in column A with 506 rows. I used following formula to check the occurance of "\" i column B, count ranges from 0-66
=LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))
I need help to code following logic
- Find "\" in the string
- Find the Space just before the "\" and split
I used following code but it doesn't serve the purpose
Range("A1:A506").Select
Selection.TextToColumns
Please help with a code that keeps points 1 and 2 in mind.