reverse array value in one cell

[deeproject]

dear mr excel

i have problem in one cell lets say G1 i have value
[TABLE="width: 1790"]
<tbody>[TR]
[TD="width: 1790"]106.97259485721588,-6.261230615140504 106.97383940219879,-6.261321266258494 106.97372138500214,-6.2627076931611905 106.97363018989563,-6.263576874303437 106.97224080562592,-6.263475558784672 106.97209596633911,-6.265133931381403 106.97197258472443,-6.266840289967433 106.97088897228241,-6.26676030453337 106.97098016738892,-6.265421879784136 106.97081387042999,-6.265331229378197 106.97094261646271,-6.2635715419082025 106.97075486183167,-6.26347022638842 106.97057783603668,-6.262862332856738 106.97038471698761,-6.262627707093791 106.97019159793854,-6.2625957126634 106.97021573781967,-6.262230442777492 106.97100967168808,-6.262241107595301 106.97102844715118,-6.261483904989587 106.97239100933075,-6.261617215387137 106.97245270013809,-6.261206619253682 106.97259485721588,-6.261230615140504[/TD]
[/TR]
</tbody>[/TABLE]

well this happen to be longitude,latitude well i want to reverse the value to latitude,longitude
like this
-6.261230615140504,106.97259485721588 .........

please help me because i have 6000 cell like this .

thank you in advance

 

[stumac]

Re: reverse array value in one cell , please help

Based on the one cell shown a UDF like the following might work:

Function reverseLandL(LandL As String)
Dim Grp() As String
Dim rev() As String
Grp = Split(LandL, " ")
For x = 0 To UBound(Grp)
rev = Split(Grp(x), ",")
    reverseLandL = reverseLandL & rev(1) & " " & rev(0) & ","
Next x
End Function

 

[Peter_SSs]

Re: reverse array value in one cell , please help

Here is another UDF that behaves slightly differently and I think does what you have asked. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RevLatLong(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.+?)(,)(.+?)( |$)"
    RevLatLong = .Replace(s, "$3$2$1$4")
  End With
End Function

Excel Workbook
	G	H
1	106.97259485721588,-6.261230615140504 106.97383940219879,-6.261321266258494 106.97372138500214,-6.2627076931611905 106.97363018989563,-6.263576874303437 106.97224080562592,-6.263475558784672 106.97209596633911,-6.265133931381403 106.97197258472443,-6.266840289967433 106.97088897228241,-6.26676030453337 106.97098016738892,-6.265421879784136 106.97081387042999,-6.265331229378197 106.97094261646271,-6.2635715419082025 106.97075486183167,-6.26347022638842 106.97057783603668,-6.262862332856738 106.97038471698761,-6.262627707093791 106.97019159793854,-6.2625957126634 106.97021573781967,-6.262230442777492 106.97100967168808,-6.262241107595301 106.97102844715118,-6.261483904989587 106.97239100933075,-6.261617215387137 106.97245270013809,-6.261206619253682 106.97259485721588,-6.261230615140504	-6.261230615140504,106.97259485721588 -6.261321266258494,106.97383940219879 -6.2627076931611905,106.97372138500214 -6.263576874303437,106.97363018989563 -6.263475558784672,106.97224080562592 -6.265133931381403,106.97209596633911 -6.266840289967433,106.97197258472443 -6.26676030453337,106.97088897228241 -6.265421879784136,106.97098016738892 -6.265331229378197,106.97081387042999 -6.2635715419082025,106.97094261646271 -6.26347022638842,106.97075486183167 -6.262862332856738,106.97057783603668 -6.262627707093791,106.97038471698761 -6.2625957126634,106.97019159793854 -6.262230442777492,106.97021573781967 -6.262241107595301,106.97100967168808 -6.261483904989587,106.97102844715118 -6.261617215387137,106.97239100933075 -6.261206619253682,106.97245270013809 -6.261230615140504,106.97259485721588
Sheet1

 

[deeproject]

Re: reverse array value in one cell , please help

Thank you very much,

work like magic, youre my life savior.

thanks again for your help.

 

[Peter_SSs]

Re: reverse array value in one cell , please help

Thank you very much,

work like magic, youre my life savior.

thanks again for your help.

You're welcome.