Excel 2007 : Manipulating test

[AyJaydee]

Hello this is my first posting on this forum.
I am using Excel, version 2007 on my home machine, to store a large number of strings of text. I would like to be able to manipulate the text in various ways and have looked through a help file giving command words but cannot see what I want.

For example, take the phrase "the quick brown fox". I have successfully used the PROPER command to capitalise the first letter in each word. Also, I have used the SUBSTITUTE command, =SUBSTITUTE(<cell>," ","") , to remove the spaces. So I have now got TheQuickBrownFox.
However, what I would like to do is firstly count the number of words in the original string, then I would like to reduce it down to it's initials so, in this example, I am left with TQBF.
It would not be practical to do this manually, because there are so many entries. Can anyone help please?
Thank you

[dilipandey]

Hi AyJaydee,


Welcome to the forum.

See the below link of our forum :-

http://www.excelforum.com/excel-work...ase-words.html

Regards,
DILIPandey

<click on below 'star' if this helps>

[AyJaydee]

Hello DILIPandey
Thank you for that, I had a look at the link you suggested and felt that it was not appropriate for what I needed. It seemed as though those solutions were directed towards cellc where the entire text was either upper case or lower case, wheras what I have is a mixture and I just want to excract individual characters. Also, I don't know how to use subroutines.
In the end, what I did was to use successive cells, each one extracting one letter from the previous cell, so all I was left with was the uppercase characters.

A B C E H I AG
=SUBSTITUTE(A3,"'","") =PROPER(B3) =SUBSTITUTE(D3," ","") =SUBSTITUTE(E3,"a","") =SUBSTITUTE(H3,"b","") =SUBSTITUTE(AF3,"z","")
Keepin' the summer alive Keepin the summer alive Keepin The Summer Alive KeepinTheSummerAlive KeepinTheSummerAlive KeepinTheSummerAlive KTSA
dark side of the moon dark side of the moon Dark Side Of The Moon DarkSideOfTheMoon DrkSideOfTheMoon DrkSideOfTheMoon DSOTM


It looks laborious, but it did not take very long to produce and with the intermediate columns hidden, the end result looks quite good.
Thanks for you help.