when using =LEFT and =MID function in EXCEL I am loosing spaces (BLANKS)?

Question

I am pasting VERY VERY long paragraphs into WORD MERGE files.  Word only allows 255 characters so I am using LEFT 255 followed by =MID(J3,256,255) =MID(J3, 511, 255) =MID(J3, 766, 255) Etc...

This works most of the time except when MID 256+255 ends with an actual word.  This lean 766 starts with a SPACE or a blank and EXCEL removes them.

I then have to run the entire merge in spell check to find the words that are messed up.  

Is there a way to keep starting spaces?

Thanks

CDV · Accepted Answer

Try substituting all your spaces with an underscore or a backslash (these are rarely used in text). When successfully transfered just use Find and replace and replace them with a space.
Formula is =SUBSTITUTE(J3," ","&#92;") or =SUBSTITUTE(J3," ","_")

dewcoons · Answer

Not aware of a way to keep the leading space.  About the only thing I can think is to test the string in an IF statement.  If the leading character is a space, then you add a space to the string before you add the =MID(whatever) to the string.

willwerth · Answer

subsequently i might use the MID function for all your extractions. If what you're finding for is foe occasion a three letter phase only use =MID(A1,seek("xxx",A1),3) in spite of if the needed text textile phase is on the initiating of the text textile string. Use discover in case you pick an exact tournament (capitals and so forth) or seek (any format) yet another formula version must be this =MID(A1,seek(B1,A1),LEN(B1)) enter needed string in B1. Use correct cellular references ie absolute , relative) to get the mandatory phase to extract.

Trending News

when using =LEFT and =MID function in EXCEL I am loosing spaces (BLANKS)?

3 Answers