Modified Formula/Macro for Text

  • Hello,


    I am wondering if the formula..


    Code
    1. ="Record " &C3


    ...can be somehow modified to pull numbers and include this around the numbers. For example, "<<FAXNUM:5826328962>>". So that the numbers will always have the <<FAXNUM: >>" surrounding it. Can this be done in a formula or does a replace macro need to do the trick?


    Thank you for any help!

  • Re: Modified Formula/Macro for Text


    Hello,


    Is there a way to macro a copy '3129653965' but when it pastes, the format is "<<FAXNUM:"3129653965">>"?


    The program will only recognize a cell with that data, not a formatted cell.

  • Re: Modified Formula/Macro for Text


    Assuming you want to indicate with your last sentence that you need to use Option 1 as per jindon, aka you copy the number and want to paste a text with the pre- and post-fix - how do you want the vba code to distinguish between fax numbers and any other text you copy/paste into Excel?

  • Re: Modified Formula/Macro for Text


    The fax number would always be in a specified column, for example, let's say column B. So the copy/paste format would always pull or format from column B of Sheet1.

  • Re: Modified Formula/Macro for Text


    Well, I used your #2 option


    "2) Case Cell format, numbers in cell remain as Number
    Custom Format "<<FAXNUM:"0">>""


    Which is Sheet1. However, I need to copy/paste using VBA from Sheet1 to Sheet2, but the pasted data needs to contain the formating as the actual text. The formatting copies over, but it's not the actual text in the cell. .. If that makes any sense lol

  • Re: Modified Formula/Macro for Text


    Perfect! With the custom format, can that also be applied to text? For instance.. A custom format for "<<TONAME:"0">>"?


    I know that this doesn't work, but is there a substitute?


    Thank you so much!

  • Re: Modified Formula/Macro for Text


    Do you mean <<FAXNUM:1234567>> to be replaced with <<TONAME:1234567>> ?


    Copy numbers only and paste
    change cell format, custom format "
    <<TONAME:"0">>"


    Is this what you wanted?


    BTW, if you use .Text property, you need to be very careful.
    When the width of the cell is not enough to display all the contents and showing like ######,
    the destination cell also shows #####.

  • Re: Modified Formula/Macro for Text


    I mean.. can you format a cell that has text to always appear as <<TONAME:Text>>?


    So, similar format to the fax concept, but with text...


    EDIT - Just figured it out.. Needed an "@" instead of "0". Thank you.

  • Re: Modified Formula/Macro for Text


    Can be done, but is it worth to do it?