In a cell Vertically entered mobile number , I want to extract all mobile number.

[aligahk06]

Dear Sir,

in an Excel Range L3:L500 entered mobile number , I want to extract all mobile number separates by semi colon.
Plz Help

Rgds,
Aligahk06
e.g 5623417825;5236648751 (format )

[mancubus]

Based on the format that you provided:
Select range in place or copy to another location.
Data, Text To Columns, Delimeter, SemiColon

https://support.office.com/en-nz/article/Split-names-by-using-the-Convert-Text-to-Columns-Wizard-2cd989db-2b1f-4d89-b17b-534250ff9905

[aligahk06]

Dear Sir,
My question is exporting all mobile number to a notepad or any other place and automatically separate each mobile number by semi colon. data in excel cell



9631382760


7549313378


9801100704


9939768722


9931969931


9771598442


9939090123


9955024512


9973933973


9801100117


9939675430


7764974516


7543058528


8002080388


8757968298

9631382760


7549313378


9801100704


9939768722


9931969931


9771598442


9939090123


9955024512


9973933973


9801100117


9939675430


7764974516


7543058528


8002080388


8757968298







For e.g : the result look like 8545126324;8545126321;8545126721 and so on.

in an Excel Range L3:L500 entered mobile number , I want to extract all mobile number separates by semi colon.
Plz Help

Rgds,
Aligahk06
e.g 5623417825;5236648751 (format )

[mancubus]

so what you want is the opposite.

assume range L3:L350 contains numbers.

in M3 insert formula =L3
in M4 insert formula =L3&";"&L4
copy this formula from M5 down to M350.
select M350
click Home tab. click Copy in Clipboard group. (or press Ctrl and C keys)
select a blank cell, click small triangle on Paste command. click Values under Paste Values.
copy this cell (Ctrl+C)
open a blank notebook file.
Ctrl+V.
save this file.

[mancubus]

and below is a VBA solution.



Sub vbax_52517_RangeToTxtFile()

Dim LastRow As Long
Dim MobText As String
Dim MobNum

With Worksheets("Sheet1") 'change Sheet1 to sheet name in your workbook
LastRow = .Range("L" & .Rows.Count).End(xlUp).Row
MobNum = .Range("L3:L" & LastRow).Value
End With

MobText = Join(Application.Transpose(MobNum), ";")

With CreateObject("Scripting.FileSystemObject")
With .CreateTextFile(ThisWorkbook.Path & "\MobNumText.txt", True)
.WriteLine (MobText)
.Close
End With
End With

End Sub


if you get Permission Denied error follow the step as described here:
http://stackoverflow.com/questions/10619805/vb6-excel-application-object-permission-denied