How to Enter a Space in a Macro Code

[cklausen]

In a macro, I am trying to enter a formula into a cell and this is the code I am using:

Cells(11, 10).Formula = "=LEFT(I11,(FIND(" ",I11,1)-1))"

The problem is, excel is trying to enter "=LEFT(I11,(FIND(" as the formula's text instead of the entire formula of =LEFT(I11,(FIND(" ",I11,1)-1)). The " " in the formula is the problem, but I don't know how to work around it.

The purpose of the macro is to split the text in I11 into three columns. Here is sample:

07436-72202 AGP STEERING PUMP

to 3 columns of

07436-72202
AGP
STEERING PUMP


Here is the complete macro:

Sub Format()


'Delete Section

Range("B10:H10").Select
Selection.Delete Shift:=xlUp
Range("B10").Select

'Insert Formulas

Cells(11, 9).Formula = "=TRIM(A11)"
Cells(11, 10).Formula = "=LEFT(I11,(FIND(" ",I11,1)-1))"
Cells(11, 11).Formula = "=TRIM(MID(SUBSTITUTE(I11," ",REPT(" ", 100)),100,100))"
Cells(11, 12).Formula = "=RIGHT(I11,LEN(I11)-SEARCH(" ",I11,SEARCH(" ",I11))-4)"
Range("I11:L4000").FillDown

End Sub

Any help is appreciated!

 

[MARK858]

You just need to double the quotes

Cells(11, 10).Formula = "=LEFT(I11,(FIND("" "",I11,1)-1))"

and the same with the other formula.

 

[cklausen]

Thanks for the response MARK858.

When you double the quotes, it doesn't enter a space in the formula. The formula I am trying to enter requires it to look for the first space in the cell and pull the text to the left of the space.

Any other suggestions?

 

[Fishboy]

Thanks for the response MARK858.

When you double the quotes, it doesn't enter a space in the formula. The formula I am trying to enter requires it to look for the first space in the cell and pull the text to the left of the space.

Any other suggestions?

Try this modification to Mark858's suggestion
Cells(11, 10).Formula = "=LEFT(I11,(FIND("&" "&",I11,1)-1))"

 

[MARK858]

Thanks for the response MARK858.

When you double the quotes, it doesn't enter a space in the formula. The formula I am trying to enter requires it to look for the first space in the cell and pull the text to the left of the space.

Any other suggestions?

Sub sss()
Cells(11, 10).Formula = "=LEFT(I11,(FIND("" "",I11,1)-1))"
End Sub

Works fine for me

Excel Workbook
	I	J
11	4444 mmmk	4444
Sheet1

 

[RoryA]

Mark's suggestion is correct. You should have two double quotes, then a space then two more double quotes in the code.

 

[cklausen]

That did work Mark. I can't believe it was that simple! Thanks everyone for the help.

 

[Fishboy]

Sub sss()
Cells(11, 10).Formula = "=LEFT(I11,(FIND("" "",I11,1)-1))"
End Sub

Works fine for me

Sheet1

	I	J
11	4444 mmmk	4444

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:137px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula J11 =LEFT(I11,(FIND(" ",I11,1)-1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Actually now that I test Mark's code, it is fine as it is without needing the &'s

 

[MARK858]

I can't believe it was that simple!

They normally are (once you know the answer ). Happy it helped and welcome to the forum if nobody has already said it.

@Fishboy

Cells(11, 10).Formula = "=LEFT(I11,(FIND("&" "&",I11,1)-1))"

will produce the formula

=LEFT(I11,(FIND( ,I11,1)-1))

in the cell which won't actually return the characters to the left of the space.

 

[cklausen]

Thanks again Mark. This is my first post, but I have been learning from this site for a while! Glad you all are around to help out.