How to remove spaces between letters in a cell

[nageshpolu]

Very urgent requirement

could someone help me in finding how to remove spaces between letters in a cell example

A B C Corporation = i want it to be ABC Corporation
A B C L Corp = i want it to be ABCL Corporation
A B Limited = AB Limited
X 1 Technologies = x1 technologies
N A F L Company = NAFL Company


hope some one could help me asap.

macro or formula = anything is good

[stojko89]

The Ctrl+F the replace function could help you in one way but wouldn't make the A B C Corporation = ABC Corporation but ABCCorporation

[contaminated]

Just press Ctrl + H In find what field type a space then press Replace All. See result.
If it won't help, place cursor in find what field then hold down Alt key and type 0160 then press Replace All. See result.

[jindon]

Try the attached

[nageshpolu]

no..... it didnt hlp me in any ways

[nageshpolu]

let me check the attached sheet

[stanleydgromjr]

nageshpolu,

Detach/open workbook RemoveLeadingSpaces - nageshpolu - EF867473 - SDG15.xlsm and run the RemoveLeadingSpaces macro.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the RemoveLeadingSpaces macro.

[nageshpolu]

hey thanks for that....

but when i have account names like I T C ..... i am unable to remove the space between these characters
and when i have company name like toshiba corporation ..... last word is getting deleted.

could you please modify the sheet.

[jindon]

Then replace the code with

Please Login or Register  to view this content.

[nageshpolu]

Hey dont mind one more small change required

.... when i use this code... it is even closing last letter in the word...
for example : abc bearings p = i am getting abc bearingsp but it shd be abc bearings p

last one please dont mind.... thank you.

[jindon]

try

Please Login or Register  to view this content.

[nageshpolu]

Man you are too genious.... thanks a ton!

could you please suggest me some books where i can refer codes like this... so that i can start scripting the code

and i have tried the last code but there is small bug in that too like

abc p l is converting to abc pl

so hope this would last bug clearance

[jindon]

If you goole with the key words like

VBA Regular Expression Meta character

You will find many good sites that you can learn from.

[jindon]

and i have tried the last code but there is small bug in that too like

abc p l is converting to abc pl

so hope this would last bug clearance

Then can you describe logically what should be done with the single letters?
Upload many different patterns of sample data with the result would help.

[Nairobi Nice]

Hi Jindon

I want to adapt your code for my worksheet.

The data was run through SPSS so everything is merged eg "Fanta Strawberry 40 ml" is now "FantaStrawberry40ml"

What would be the code to merge everything in a cell?

[jindon]

Nairobi Nice

You need to open a new thread for your own.(according to the rule here)
When you open, attach a workbook with the data before/after format and it will help to understand your problem.

[Nairobi Nice]

Noted Jindon

Thanks

http://www.excelforum.com/excel-prog...68#post2968568