string concatenation

I have a string which contain spaces in between. I want all the spaces to be remove.

Example: Say i have a string "IN DI A" and i want it to set as "INDIA".

Can some help me on this using VB.

I have 100 string to which spaces are to be removed and are to be kept in the same column.

Hi

This will remove all the spaces in column A.

Please Login or Register  to view this content.

rylo

I have one more problem.

If the data is in format as mentioned below:
IN
DIA

and i want to be it as INDIA then how it is possible

In the same cell, or adjacent ones?

In the same cell.

Hi

Which cell do you want it in, and what do you want to do with the data from the other cell?

rylo

I have data in the cell C3,C5,C6 which is of the format,

Cell C3:IN
DIA

Cell C5: D
ELHI

Cell C6: 12
3

I want these cells to be displayed as:

Cell C3: INDIA , Cell C5: DELHI, Cell C6: 123.

This formula should do
=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(A7);LEN(TRIM(A7))-OR(RIGHT(TRIM(A7))={"?";"!";"."}));CHAR(160);" ")))
( from Ron de bruin's site)

Replace ; with , as needed

its not working properly and giving some or the other compilation error.

Please add a sample of your data in an XL sheet

the data to be accessed is:

07AC
TSTAT=1,07MSISDN=9848--,07PROFILE=127---,07SPID=227,07VMS_PWD=6885------,07LAN
GUAGE=0,07SERV_TYPE=1-,07ANI_NR1=####################,07ANI_NR2=98481206--------

the VB code is:
Sub Split_String()

Dim arTemp() As String
Dim s1
Dim s2
Dim s3
Dim i1

Range("A2:D100").ClearContents
Range("A1:D100").Replace what:=" ", replacement:=""

arTemp = Split(Cells(1, 1), ",")
For i1 = 0 To UBound(arTemp)
s1 = Left(arTemp(i1), 2)
s2 = Mid(arTemp(i1), 3, InStr(1, arTemp(i1), "=") - 3)
s3 = Mid(arTemp(i1), InStr(1, arTemp(i1), "=") + 1, Len(arTemp(i1)))

Cells(i1 + 2, 1) = "'" + s1
Cells(i1 + 2, 2) = s2

If Left(s3, 1) = 0 Then
Cells(i1 + 2, 3) = "'" + s3
Else
Cells(i1 + 2, 3) = s3
End If

Next i1

End Sub

Please update me with the solution.
Thanks to all.