Add double quotes to cells that contain spaces

[nicluc88]

Hi,
how can I find the cells that contain the spaces and optionally add the double quotes?

I'm writing a script to extract a sheet from an excel file, and save in a new CSV:

Workbook.SaveAs FileName:=FileName, FileFormat:=xlCSVUTF8, Local:=True

Thanks

[nicluc88]

no idea?

[rlv]

no idea?

I can only speak for myself, but I think you are not getting any responses because your question is missing too much information. There's nothing there for a responder to engage with so they move on. In a perfect world, someone would ask you a lot of questions about what you are trying to do and slowly, after enough back and forth posting, enough details about what you want would emerge to allow the problem to be worked. But that takes energy and time, and sometimes people are just not in the mood, and so your question is skipped over. I think you would get a better result if you were to start again and create a new and more detailed description of the problem you are having, what your specific desired outcome is (perhaps along with an example to illustrate), and a description of any steps taken so far. People who's posts contain a clear problem description along with relevant data, sample inputs, and sample outputs usually get more attention. You should not assume that we know anything about what you are doing and so you should explain everything. Something that seems "obvious" to you is not necessarily obvious to anyone else.

If you have it, post code that you are having trouble with. Or a sample workbook showing some sample data.

[nicluc88]

I can only speak for myself, but I think you are not getting any responses because your question is missing too much information. There's nothing there for a responder to engage with so they move on. In a perfect world, someone would ask you a lot of questions about what you are trying to do and slowly, after enough back and forth posting, enough details about what you want would emerge to allow the problem to be worked. But that takes energy and time, and sometimes people are just not in the mood, and so your question is skipped over. I think you would get a better result if you were to start again and create a new and more detailed description of the problem you are having, what your specific desired outcome is (perhaps along with an example to illustrate), and a description of any steps taken so far. People who's posts contain a clear problem description along with relevant data, sample inputs, and sample outputs usually get more attention. You should not assume that we know anything about what you are doing and so you should explain everything. Something that seems "obvious" to you is not necessarily obvious to anyone else.

If you have it, post code that you are having trouble with. Or a sample workbook showing some sample data.

Hi rlv, thanks for your reply.
I agree with your message; the request was not a support in the code, but some ideas about how to solve it.

Anyway, I follow your advice

Goal: If a cell contains space (" "), I want to put it on the top before and after.

Sub Macro1()
For Each myCell In ActiveSheet.Range("A2:W7957")
If InStr(myCell.Value, " ") > 0 Then
myCell.Value = Chr(34) & myCell.Value & Chr(34)
End If
Next myCell

 ActiveWorkbook.SaveAs Filename:="C:\Cartel1.csv" _
        , FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub

my simple script, on a few cells responds quickly, on about 8000 lines and 23 columns the script stays running for too long (about 20 minutes).

Is there a more optimized alternative?

[rlv]

I created a worksheet with data in cells A2:W7945, each cell containing a string with a space. I ran your code on it. It took just 7.6 seconds to complete all the cells, so if yours takes 20 minutes, it suggests that the root cause of the slowness lies elsewhere. What else are you doing in your worksheet that could be causing it? Have you tried turning off calculation while looping through the cells?

Sub Macro1()
    Dim ST As Single
    ST = Timer
    Dim MyCell As Range

    Application.Calculation = xlCalculationManual
    For Each MyCell In ActiveSheet.Range("A2:W7957")
        If InStr(MyCell.Value, " ") > 0 Then
            MyCell.Value = Chr(34) & MyCell.Value & Chr(34)
        End If
    Next MyCell
    Debug.Print "Elaped time for X = " & Timer - ST
    Application.Calculation = xlCalculationAutomatic

    ActiveWorkbook.SaveAs FileName:="C:\Cartel1.csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub

[nicluc88]

I created a worksheet with data in cells A2:W7945, each cell containing a string with a space. I ran your code on it. It took just 7.6 seconds to complete all the cells, so if yours takes 20 minutes, it suggests that the root cause of the slowness lies elsewhere. What else are you doing in your worksheet that could be causing it? Have you tried turning off calculation while looping through the cells?

Sub Macro1()
    Dim ST As Single
    ST = Timer
    Dim MyCell As Range

    Application.Calculation = xlCalculationManual
    For Each MyCell In ActiveSheet.Range("A2:W7957")
        If InStr(MyCell.Value, " ") > 0 Then
            MyCell.Value = Chr(34) & MyCell.Value & Chr(34)
        End If
    Next MyCell
    Debug.Print "Elaped time for X = " & Timer - ST
    Application.Calculation = xlCalculationAutomatic

    ActiveWorkbook.SaveAs FileName:="C:\Cartel1.csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub

Thanks rlv,
adding the following 2 lines, the script runs in few seconds

   Application.Calculation = xlCalculationManual
[...]
   Application.Calculation = xlCalculationAutomatic

Now, I go to study this new (for me) command.

rlv, your contribution is really appreciated! Thanks again

[nicluc88]

Now, in the output csv, the double quotes increase:

"""Phase 1"""

I expected to find only 2 symbol, 1 before and 1 after string value...

"Phase 1"

[rlv]

    For I = 5 To 2 Step -1
        With ActiveSheet.Range("A2:W7957")
            .Replace What:=String(I, 34), Replacement:=Chr(34), LookAt:=xlPart
        End With
    Next I

[nicluc88]

    For I = 5 To 2 Step -1
        With ActiveSheet.Range("A2:W7957")
            .Replace What:=String(I, 34), Replacement:=Chr(34), LookAt:=xlPart
        End With
    Next I

Hi rlv,
in the sheet, the cell value is without double quotes, only in the output csv; so this procedure is to run on the csv, right?
Thanks