Sishir Roy

Hello ANDRES,
Thank you for reaching out to us. It appears that you want to store the username (which is the first column) and only the numeric values in your Excel file from your text file.
To achieve this, you can use the following code:

Sub Import_Selected_Data_To_Excel()
    Dim importFileNum As Integer, rowNumber As Integer, columnNumber As Integer
    Dim importFileLocation As String, textDelimiter As String, textData As String
    Dim textArray() As String, splitArray() As String, outputData As String

    importFileLocation = Application.GetOpenFilename()

    textDelimiter = ","

    importFileNum = FreeFile
    Open importFileLocation For Input As importFileNum

    textData = Input(LOF(importFileNum), importFileNum)

    Close importFileNum

    textArray() = Split(textData, vbLf)

    For rowNumber = LBound(textArray) To UBound(textArray) - 1
        If Len(Trim(textArray(rowNumber))) <> 0 Then
            splitArray = Split(textArray(rowNumber), textDelimiter)
            If UBound(splitArray) >= 1 Then
                outputData = outputData & splitArray(0)
                For columnNumber = 1 To UBound(splitArray)
                    If IsNumeric(splitArray(columnNumber)) Then outputData = outputData & vbTab & splitArray(columnNumber)
                Next columnNumber
                outputData = outputData & vbCrLf
            End If
        End If
    Next rowNumber

    If Len(outputData) > 0 Then
        outputData = Left(outputData, Len(outputData) - 2)
        ActiveSheet.Cells.Clear

        Dim dataArray() As String, rowArray() As String
        dataArray = Split(outputData, vbCrLf)
        
        For rowNumber = 1 To UBound(dataArray) + 1
            rowArray = Split(dataArray(rowNumber - 1), vbTab)
            For columnNumber = 1 To UBound(rowArray) + 1
                ActiveSheet.Cells(rowNumber, columnNumber).Value = rowArray(columnNumber - 1)
            Next columnNumber
        Next rowNumber
        
        MsgBox "Data Imported Successfully", vbInformation
    Else
        MsgBox "No Data Has Been Found", vbExclamation
    End If
End Sub

This code will import multiple rows of data with the username and numeric values from your text file into Excel.

For demonstration, I have created a text file like the image below:

After running the code and choosing the text file, you will get an output like this:

Let me know if you have any questions or if there’s anything else I can help you with!

Regards,
Sishir Roy

Hello LILY,
The formula used in the section “How to Include Space Between Numbers and Text in Excel” is a complex formula designed to insert a space after the rightmost numeric sequence in the text in a cell and then trim any extra spaces. It does not insert a space before the numeric sequence and only inserts a space after the rightmost numeric sequence. For example, you can see the image below.

The issue you are facing might be due to certain functions not working correctly in your Excel version. Consider using the latest version to avoid these problems.
However, the formula in the provided section does not insert space before the numeric sequence, making it less versatile. If you wish to insert a space between text and numbers regardless of the order of the numeric sequence, I can provide you with a VBA code to address this. If you want to insert a space before and after each numeric sequence without spaces between individual digits, you can use the following VBA function:

Function InsertSpacesAroundNumbers(inputText As String) As String
    Dim outputText As String
    Dim i As Integer
    Dim num As String
    Dim insideNumericSequence As Boolean

    outputText = ""
    insideNumericSequence = False

    For i = 1 To Len(inputText)
        num = Mid(inputText, i, 1)

        If IsNumeric(num) Then
            If Not insideNumericSequence Then
                outputText = outputText & " "
                insideNumericSequence = True
            End If
            outputText = outputText & num
        Else
            If insideNumericSequence Then
                outputText = outputText & " "
                insideNumericSequence = False
            End If
            outputText = outputText & num
        End If
    Next i

    outputText = Trim(outputText)

    Do While InStr(outputText, "  ") > 0
        outputText = Replace(outputText, "  ", " ")
    Loop

    outputText = Trim(outputText)
    InsertSpacesAroundNumbers = outputText
End Function

To use this, open the VBA launcher by pressing the Alt+F11 button on your keyboard.
Click on the Insert button and choose the Module option.
In the Module section, paste the provided VBA code.

After that, go back to your Excel file, and type the function name ‘=InsertSpacesAroundNumbers‘. You will find the function name in the function list. Put the cell reference in the function argument and press Enter.

As a result, it will insert a space before and after each numeric sequence without inserting spaces between individual digits.

Regards,
Sishir Roy

Hello ANNAWHO,
The workbook (the one that is indicated by the ‘FilePath’) that is opened and changed with new data will be saved before it is closed in the supplied code of this article. The line “ActiveWorkbook.Close True” indicates that the file will be saved with any changes made to it before closing.
However, the workbook containing the VBA code itself, the file where the macro resides, will not be saved as a result of running this code. The workbook that contains the code isn’t saved by the code itself.

Best Regards,
Sishir Roy