How to do this Multiple variations of a string in cell A2 , A3, A4 , A5 and A6 and so on

My Requirement is

I have a String in A1
CHEMICAL, PETROCHEMICAL MECHANICAL AND METALURGICAL TRAINING CENTRE

I want
A2 : CHEMICALPMAMTC [string before the comma followed by all the First Letters without spaces]
A3 : CHEMICALPETROCHEMICALMECHANICALMETALURGICALTRAININGCENTRE
A4 : C H E M I C A L , P E T R O C H E M I C A L M E C H A N I C A L A N D M E T A L U R G I C A L T R A I N I N G C E N T R E
A5 : C H E M I C A L , P E T R O C H E M I C A L A N D T R A I N I N G C E N T R E [REmove any two words if the number of words longer than 5 in the given string]
A6 : CHEMICALXXXPETROCHEMICALXXMECHANICALXXXANDMETALURGICALXXXTRAININGXXXCENTRE [A three character string between each word and no spaces]
A7 : Random special characters in between string - spaces stripped
A8 : Split the A6 - at random locations but put in multiple lines in the same ceel like [CHE MICAL CHAR(12) PET RO CHEM CHAR(12) ICAL and so on]
A9 : Content of A6 0 -> Replace the character which occurs very minimum in the string with another alphabet [A to E]


This is my first question in the forum, hoping to get a solution.

For my requirement of A2 I had worked a solution to split the name into multiple part using the SPACE and use MID of 1 to get the tags as I need.
Hence A6 is also resolved automatically just by concatenating the text together.

For my requirement for A3 I got the solution it goes like this

Please Login or Register  to view this content.

Here's up to A6.

Need to see the result from A7 to A9 in workbook.

Please Login or Register  to view this content.

Thank you pike and jindon.
shall check this and revert.

Jindon ..will this script work for range?
/

The code I posted works only for the data in A1.

Posting a workbook with before/after format with good explanation would help.

HI jindon,

The script works as expected without any single issue.

I had made few corrections to represent the data in a SINGLE row rather than a single column.

Had given a few headers for my reference.

I would like this to work for a range I select COLUMN A - all the data should be populated in column B to F [G..H ..J].

Thank you for the quick response on this. Pasted the code and attached the file I used to test this ..Updated the Sub in the attached file also.

Please Login or Register  to view this content.

Regards
Sundharesan R

Please Login or Register  to view this content.

You'd better upload a variety of pattern of string and the result that you want.

I don't want to continue this without seeing actual data pattern and the result.

There is a difference in result.

You eliminated "AND" from some of your original result, but not in your attached (&).

I need more different example data to be processed and EXACT result that you want.

Okay - Shall update it with a completely a different string. Sorry for that.

Jindon,

I had put three examples now

& - actually in the original string & to be stripped. and i had included special characters range in another requirement.

Attached the new file.
Regards
Sundharesan R

Explain more about stripping "AND", "&".

It doesn't seem to be consistent.

Jinton

Pleasr strip & if it is part of name.
in one sample specific set of special characters are there to inclodr in string
AND : It is a normal word. as it happen to be part of a missing word of is not there in one of the samples.

Treat and as a word.
Regards
Sundharesan

Jindon,

I am extremely sorry for the much delayed response.

As such the issue of the error is on my part - if there is COMMA in the INPUT it results in a error.

Otherwise your script is works like a magic and does all the tasks for 10,000 strings in quick time.

Thank you for that.

Now to the issue of AND and &.

Please remove ALL special characters in the INPUT String.
AND - Treat it as a normal word like another word.

Regards
Sundharesan R

HI jeffreybrown,

Can you please close this thread. I will open a fresh one.

The solution given my Jinden works fine for first 6 requests.

I will start a new thread for the remaining.

Regards
Sundharesan R
Also guide me how to post closure comments and give a rating or STARs to the person who helped.