[Solved] Joining text and values

    Have looked at the help documentation for joining text and values but still don't understand the spacing for a long string of text with as many as 10 values to be interspersed in the text.
    The next issue is the example allows a conversion of a number to a different value ie
    TEXT("4/15/91", "mmmm dd, yyyy")
    which results in the date April 15, 1991
    What I want to do is bring the date in to the formula from another cell
    Any help would be greatly appreciated

    Hi - welcome to the board.


    Your post's not entirely clear, so forgive me if I've mis-interpreted your problems...


    Quote


    Have looked at the help documentation for joining text and values but still don't understand the spacing for a long string of text with as many as 10 values to be interspersed in the text.


    the ampersand is the concatenation operator, so sometihng like:


    ="text "&a1&" "&B1&" "&C1&" more text"


    ...etc


    Quote


    The next issue is the example allows a conversion of a number to a different value ie TEXT("4/15/91", "mmmm dd, yyyy")
    which results in the date April 15, 1991


    the text() function allows you to apply custom number formats to values. It returns a text string. If you want to use the date in further calculations, don't use the text() function, or at least don't refer to the results of the text() funciton in the calculations - use the original date.



    Quote


    What I want to do is bring the date in to the formula from another cell



    as above - just use the original date value, not the text version.


    HTH


    paddy

    Paddy thank you for the answer
    in the first instance I understand the way to write the formula.
    The second I was not clear
    I downloaded data from a souce as a text file into excel. I then transfer that information to another cell in the spread sheet. this cell is then connected to a form in a different program at that point the number shows up as a number like 36590 ( I know this is the excel method of caculating dates) so a date of 4/15/91 goes in ata number like that but if i can make the date read April 15, 1991 it will transfer in. What I need is the formula fo tranfering a date from cell a1 into the conversion formula that allows the date to show up as above.

    I don't think you need a formula to 'convert' the value. would simply formatting the cell suffice? (right click | format cell... | select the appropriate date format.

    Formatting works in excel but the program that the date is tranferred into shows the number 35660 not the date and it cannot be reformatted thus the need to make the date read differently.

    "so a date of 4/15/91 goes in ata number like that but if i can make the date read April 15, 1991 it will transfer in"


    sorry - misread your post. you need the format for the transfer to the other program!


    as you posted, the text() function can be used to return a formatted text string. If you have a propre date value in the cells, then simply:


    =text(a1,"ddd dd/mm/yy")


    or whatever should suffice.

    Paddy thank you so much you are the greatist thing since sliced bread I may not be back for awhile because i only need formatting help once in a blue moon
    Spike

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login