removing spaces (1 Viewer)

[teel73]

Is there a way to strip spaces that are in the middle of a string? I know the LTrim,RTrim & Trim functions for trailing, leading & both spaces but what about in the middle. For example:

The sentence below was imported from a spreadsheet into one field and it treated the hard line feed as a huge space. So now I need to figure out how to put "....-You begin your assessment and management of the situation because you b" on a new line in that field.


this is currently what the sentence looks like in the field:

You are called to the scene of a middle-aged man who has “passed out” in the hallway. Upon arrival the scene is safe, and the patient remains unresponsive. -You begin your assessment and management of the situation because you b


I want to update the data to look like this:

You are called to the scene of a middle-aged man who has “passed out” in the hallway. Upon arrival the scene is safe, and the patient remains unresponsive.

-You begin your assessment and management of the situation because you b

 

[jfgambit]

Do a search on the "instr()" function. That should get you moving in the right direction.

 

[teel73]

Ok. Thanks. I used the instr function but now my value displays a symbol instead of the actual line feed. How do I get my query to recognize the chr(10)?

Here's what I have in my query:

Left([Full Question],InStr(1,[Full Question]," ")) & Chr(10) & Trim(Mid([Full Question],InStr(1,[Full Question]," ")))

Where I have "chr(10)" for the line break it places a little square in the field.

 

[jfgambit]

Pair it with Chr(13)...

Left([Full Question],InStr(1,[Full Question]," ")) & Chr(13) & Chr(10) & Trim(Mid([Full Question],InStr(1,[Full Question]," ")))

 

[MarkK]

You might also look into the Replace() function. Syntax in this case might look like ...

Replace(FullQuestion, "  ", vbCrLf)