Parsing text with VBA

I'm looking to write a macro that can take text from one cell and set a user defined number of spaces between it along with the text from another cell. I did it manually and used the macro recorder, but it doesn't really help
ie

Sub Parse_Text()
'
' Parse_Text Macro
'

'
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=RC1&""        ""&RC2"
    Range("E5").Select
End Sub

As I want to be able to tell the macro how many spaces I want between the text.
Eventually I will write the parsed text to a text file.

Is there a way to do this with VBA? Can someone just point me in the right direction?

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

hi Carnifex,

Yes, VBA can do this.
Look up "WorksheetFunction.Rept Method" in the VBE Help files, and create a variable that is populated by an Input box to provide the second argument to the REPT method.

btw, your existing code can be shortened to the below by removing the first ".select":

Sub Parse_Text()
    Range("E4").FormulaR1C1 = "=RC1&""        ""&RC2"
    Range("E5").Select
End Sub

hth
Rob

Look up WorksheetFunction.Rept

Or see VBE Help for the native String function.

Hello Carnifex930,

Welcome to the Forum!

I think this what you are after. This macro is a User Defined Function. It works similarly to a worksheet function. It will create the new text using the cells and the number of spaces you provide. Copy this code into a VBA module.

NOTE: Changing the values of either cell will not change the UDF result. If you need this to happen, let me know.

Function ParseText(ByVal Spaces_To_Add As Integer, Cell1, Cell2)
  ParseText = Cell1 & Space(Spaces_To_Add) & Cell2
  ActiveCell.Offset(1, 0).Select
End Function

UDF Example

   'In Cell E4 
    =ParseText(10,E1,E2)

   'Results - Displays E1 and E2 with 10 spaces in between

Adding the Macro

1. Copy the macro above 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. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Thanks for all the help. Maybe I can get just a little more advice.

The function that Leith posted:

Function ParseText(ByVal Spaces_To_Add As Integer, Cell1, Cell2)
  ParseText = Cell1 & Space(Spaces_To_Add) & Cell2
  ActiveCell.Offset(1, 0).Select
End Function

Does the trick. However, I'm looking to do this on a larger scale; for now I would like to assume just one row of data (text in each cell).

Say we have 4 cells. Cell1, Cell2, Cell3, Cell4

The program should be able to define a string variable,and store Cell1's text in it.
Next a function will be used to take Cell2's text and the string variable(has cell1's text in it), which it will then parse it a number of (known) spaces and then store back into the string variable, the moves on to Cell3 take its text and parses it a number of spaces with the string variable (String Variable now has Cell1's text parsed corecctly with Cell2) and the stores it back into the String Variable.

The end result is a string variable that Contains the text from Cell's 1,2,3 and 4 parsed correctly.

Sorry I'm new to VBA, I can figure out all the loopin, I just need to adapt the above function to save into a string variable, not a cell, How do I do this?

hi Carnifex930

You may have already solved this, but just in case you haven't...
To use the function that Leith posted you can call it from another macro to define a string variable by putting it on the right of the equals sign in your code, for example:

Sub MainMacro()
dim String_Variable as string
dim rng1 as range
dim rng2 as range
'other dims...
'other code...

'define the ranges
set rng1 = activecell
set rng2 = rng1.offset(1,0)

'apply looping...
'define the string (including the string variable on the right of the _
equals sign means that it will add the new value to the existing _
value when you add your looping code). 
String_Variable = String_Variable & ParseText(9,rng1, rng2)

'other code...
end sub

Function ParseText(ByVal Spaces_To_Add As Integer, Cell1, Cell2)
  ParseText = Cell1 & Space(Spaces_To_Add) & Cell2
 End Function

hth
Rob