Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

=concatenate With A Space

How do I concatenate Cell B3 & C3 and have a space between the values in B3 & C3?

B3 C3
423 "CHANGE-OVER" ADAPTOR

=CONCATENATE(B3,C3) 423"CHANGE-OVER" ADAPTOR

I'd like a space between them 423 "CHANGE-OVER" ADAPTOR


Post your answer or comment

comments powered by Disqus
I would like a macro to copy the data in Raw1 and Raw2 from sheet1 into sheet2. but with a space row between data as shown in attrachement.

the number of rows in Raw1 and Raw2 is always the same. but the number of rows can be very big and unknown.

I have named the title of Raw1 as "raw". becasue I want the macro will still work when I insert a column before it.

what is the easiest way to do this task?

Many thanks

EDIT: Solved

Fixed it by adding a semi-colon to the end of the print. Seems Print automatically inserts a return carriage


	VB:
	
 
Sub DeleteBreaks() 
     
    Dim str As Variant 
     
     
    Open "C:Testingtest.txt" For Input As #1 
    Open "C:TestingOutbreak.txt" For Output As #2 
     
    Do Until EOF(1) 
         
        Line Input #1, str 
         
        str = Replace(str, Chr(13), "", 1) 
        Print #2, str; " "; 
         
    Loop 
     
    Close 1 
    Close 2 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


In order to later import a text file into excel and format it correctly as a CSV, I need to loop through and replace all the carriage returns denoting a new line in the text file. I would like to replace them with a space since I would like to have a single line instead of a multi-line document.

I was thinking of something along the lines of; however, I do not know how I would use the replace:


	VB:
	
 
Sub Import() 
     
    Open "c:File.txt" For Input As #1 
    Open "c:Output.txt" For Output As #2 
     
    Do Until EOF(1) 
         
        Line Input #1, str 
         
        Replace(??????????) ' Unsure what code should go here
         
    Loop 
     
    Close 1 
    Close 2 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Any suggestions? Auto Merged Post;

Bump for found solution

Hi all,

Im guessing this is simple but its driving me mad. I have 2 columns....first name in A and last name B. I want them both in C, so ive been doing:

=A&B

Which gives me: FirstnameLastname with no space in between.

How do I do this with a space in between?

Cheers

I have an extract from a Lotus Notes Database. When entering data into the
database, we used the enter key to separate paragraphs. When this data is
placed into Excel, the carriage returns appear as either a BEL character of 7
or a DEL character of 127. Not quite sure, but it shows up as a square.

I would like to remove all these characters and replace them with a space.
If I use the Clean function, all of the characters (spaces) are removed, but
this does not leave any spaces so words and paragrahs then become combined.
I have tried a combination of the clean and substitute functions without any
success.

I have some alphanumeric data imported from a Database. If the user hits the "enter" key when they type the text into this DB, I end up with a 'square' ie looks like CHAR(10) within the text.

If I use the Clean function it gets rid of it but I need to replace it with a space. I have also tried doing a replace, but can't "Find" the character

Does anyone have any suggestions?

Thanks

Karen

Howdy All,

I am wondering if someone can help me with this one.

I have values in columns A and B.
The values are numeric.
I need to have the value combined into column C with a space between them.

Example.

A1 = 12345
B1 = 98

C1 must be 12345 98

Thanks,
Brian

Wanting to replace vacant, named cells in column "D" with a space.
That is, look at all cells in column "D" with a defined name, and if there is nothing in the cell (no text or number), put in a space character " ".
Thanks.

Good Afternoon-

I download my cellular phone bill monthly into Excel. Unfortunately, the time of day data is concatenated in the following way, 12:23PM or 9:12AM. In order to sort the data, I need to place a space bewteen the time and the PM or AM.

I am not very good with VBA, so is there anyway to do this with formulas? I have tried using mid, right, and left to create a new column(s) and then re-combine but I am having no luck.

Any help is greatly appreciated.

Scott

I have a list of names that are coming out as being concatenated with no space between their last and first names.

Since every ones names are of varying lengths, I was wondering if there was a VBA code or formula that I could use to enter a space to the left of the 2nd capital letter. I have looked at the =LEFT formulas but it seems that this is based on the number of characters entered.

Current View: SmithJohn
Wanted View: Smith John

Any help in this is greatly appreciated. Thank you for your assistance in advance.

Hi I'm trying to call a sub routine in another workbook, which has a space in the name. I'm able to get it working with this:
application.run "'source test.xls'!macro1" but I'm not able to get it working if I assign the file name to a string.

for e.g. dim str as string: str = "source test.xls"
application.run "'" & str & "'" & "!macro1"

Could someone pls advise how I can get this to work ? Thank you.

Let's say I have a set of part numbers in column A. In columns B through Z are factories in which the part could be produced. If it's not produced at the factory, the column is blank, if it is, then the cell in that column has the name of the factory.

In column AA, I would like to have a "concatenate" of every factory the part is made at with a comma or some kind of separator between each factory name. The key is that it needs to search every column in B through Z and if the cell is blank, I don't want the separator because that would look funny.

Any ideas?

Thanks

Good Evening -

I have four cells, FirstName1, LastName1, FirstName2, LastName2

I would like to concatenate them into one cell, but with a line break between Name1 and Name2. It would look like:

John Smith
Susie Smith

in the same cell

I have the CONCATENATE part all figured out, but I don't know how to tell Excel to put it on the next line (not next row)

Any ideas?

Thanks!!

This is a new problem and is driving me crazy! I am installing Office 97 (word, excel only) on a windows 2003 server. I have outlook xp installed as well. Office 97 has been updated to service release 2b. Basically when you try and open an excel file three error messages will display before it opens the file. The messages are: "C:documents.xls can not be found" "C:and.xls" cannot be found" & "C:Settings.xls" cannot be found.

It seems that it has to do with the spaces in the path, but can't really change that. Here's the kicker though, I have 13 other servers that are all exactly the same and they are not acting this way. However I installed Office 97 on them about 6 months ago, since then I have built 4 servers which are displying this error. I have searched high and low and can't figure out any logical explination for this or a fix. Does anyone have any suggestions at all?

Thank-you very much!!!

Hello,

I have a range of values which are extracted from our system, in this case postcodes.

British postcodes have to follow a certain format. There can be one of 5 permutations:

M1 1AA
M27 4LA
BL1 1AA
DN26 2WX
SW1A 1AA

As you can see, what we call the "postcode unit" (the last three characters) has to be separate from the postcode area and sector (the M1/M27/BL1/DN26/SW1A).

Unfortunately, our system extracts correctly inputted data (M1 1AA for example) with the everything erroneously joined together (so it pulls M11AA/M274LA and so on instead). Our system cannot be amended to extract the information correctly.

I need Excel to put a space in the right place on my "formatted data sheet" when I paste the data in on on "raw data" sheet.

I have tried =RIGHT, =LEFT and =MID, but due to the different lengths of postcodes (smallest is a 2/3, biggest is a 4/3 as per above) this hasn't really worked for me.

Alternatively, if there is a way for Excel to cut off the last three digits of a postcode, I can then use =CONCATENATE to rejoin them together with a space.

I hope this makes sense and someone can give me a few pointers! If you need any more info let me know...

Ta,

Ben

Hi guys

My first post here!

I have two columns in my Excel worksheet - A & B. Column A contains numbers (not consecutive) and column B contains text.

What I want to do is merge column A with column B and put a space between the merged number and text.

E.g.

Column A Column B
234 XYZABC

I want a new column to contain 234 XYZABC. There are about 800 rows.

I hope I've explained this adequately!

Cheers

John

I use excel to manage my music Database. When I paste album information for my music into a cell the tracks are pasted on their own seperate line within the cell.
Example:
"Song List
1. Power Of Independent Trucking, The
2. Model, The
3. Bad Penny
4. L Dopa
5. Precious Thing
6. Columbian Necktie
7. Kitty Empire
8. Ergot
9. Kasimir S. Pulaski Day
10. Fish Fry
11. Pavement Saw
12. Tiny, King Of The Jews
13. Bombastic Intro
14. He's A Whore"

What I need to do is take that and put all information on one line within the cell seperated by a space, instead of seperate lines within the cell.
Example:
1. Power Of Independent Trucking, The 2. Model, The 3. Bad Penny 4. L Dopa 5. Precious Thing 6. Columbian Necktie 7. Kitty Empire 8. Ergot 9. Kasimir S. Pulaski Day 10. Fish Fry 11. Pavement Saw 12. Tiny, King Of The Jews 13. Bombastic Intro 14. He's A Whore"

If anyone knows how to remove the returns and place all of the information on one continuous line it would be very helpful.

John

Hi I am pulling in a bunch of names from a website. These strings all start with a space. Trying to delete the space in all. Example "Adam" is coming in as " Adam" and not pulling up a vlookup because not an exact match help please.

G'Day everyone,

Below is a formula I use to ascertain anniversary dates, however when the date filed is blank or it is not one of the aniversay dates in the array, Excel returns #N/A in the cell. How do I code to replace the #N/A with a blank.

Regards,

John.

=INDEX({" 1st Anniversary within 30 days."," 5th Anniversary within 30 days.","10th Anniversary within 30 days.","15th Anniversary within 30 days.","20th Anniversary within 30 days."},,MATCH(1,(TODAY()>=DATE(YEAR(E6)+{1,5,10,15,20},MONTH(E6),DAY(E6))-30)*(TODAY()

I am using the following to reference worksheets which works fine except for
when the worksheet name contains a space.

=INDIRECT($A8&"!AI$8")

I dont want to remove the space from the worksheet name because it will
cause all other sorts of issues. How can I get this to work? Im guess it
involves enclosing the the W/S in ' ' but I've had no sucess

Bruce

Is there a function to read values starting from the right of a cell and
pulling characters until I reach a space?

Two examples:
SGA3220R_IAOAKTREE (BTL-3) 1177 64089 - would return "64089"
SGA3321R_IAREDROAD (BTL-3) 1177 975 - would return "975"

I know I could do a Text to Columns with a space delimiter but was
hoping to find something easier. Thanks in advance for any help.

--
wolfpack95
------------------------------------------------------------------------
wolfpack95's Profile: http://www.excelforum.com/member.php...info&userid=93
View this thread: http://www.excelforum.com/showthread...hreadid=497953

Is there a function to read values starting from the right of a cell and pulling characters until I reach a space?

Two examples:
SGA3220R_IAOAKTREE (BTL-3) 1177 64089 - would return "64089"
SGA3321R_IAREDROAD (BTL-3) 1177 975 - would return "975"

I know I could do a Text to Columns with a space delimiter but was hoping to find something easier. Thanks in advance for any help.

This line works...

Set working = Workbooks.Open("L:theFolderTheEmail.xls")

this one returns a 1004 error...

Set working = Workbooks.Open("L:theFolderTheEmail.xls")

getting "smart" I tried...

Set working = Workbooks.Open("""L:theFolderThe Email.xls""")

The file in question WILL have a space in the name when we receive it. Is
there some trick to opening the file when there's a space in the name?

Maury

I am using the following to reference worksheets which works fine except for
when the worksheet name contains a space.

=INDIRECT($A8&"!AI$8")

I dont want to remove the space from the worksheet name because it will
cause all other sorts of issues. How can I get this to work? Im guess it
involves enclosing the the W/S in ' ' but I've had no sucess

Bruce

I have a data table populated from two drop down boxes
and a lookup, I have a cell that is the two drop down
boxes concatenated with a space between, is there anyway
I can set the title properties of the graph i have of the
data, so that the title of the graph is the concatenated
cell ?


No luck finding an answer? You could always try Google.