Dear experts
Say i have entered a value of 20, but the number format is 5200..
the result is 520020.
How do i copy the final result to a different cell.. I tried paste and also paste specials.. nothing work..
Is there a way to copy number formatted cell along with entered values ?
copy /paste special values then repeat copy paste special formats
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
copy paste special values show me 20
copy paste special formats show empty cell
when i do the above things into same cell.. the result are still same custom formatted cell
but i want to export 520020 as value into different cell
Hi Sultix,
In any blank cell choose Format, Cells
On the number tab choose custom
enter the following format:
You can then copy, paste special, formats to any cell.
Please Login or Register to view this content.
When you enter data into that cell you will get output of 5200data you entered
If I helped, say thanks by clicking on the star.
if you wanted the value shown in a1 to use as a number you could just use
say in b1
=--TEXT(A1,"000000")
Hi Martin,
i tried that too .. its same effect what i had before..
i dont get the formatted result if i copy afterwards
what i need is .. in any cell .. i need to get the full string number on bar aswell
copy b1 paste special values
if thats not what you want attach a workbook with some examples
regarding your second post with Text Formula it didnt work aswell... but when i replaced the formula the numbers "0000000" with "5200000".. then copy paste special values it did work on Column E.. But what if the first 4 digits are different.. i cannot change the formula everytime.. sample attached
ah i see,so the short answer is you cant,i was copying format as well.
what is the reason behind wanting to do this?
you could do it another way by copy/pasting the numbers as they are so cell shows 150 ,120 and so on
then in a spare cell put 52000000
copy that
select all the cells you just pasted and paste special add
Last edited by martindwilson; 07-02-2011 at 06:42 PM.
i enter invoice numbers even the first numbers are changing .. to enter data manually i shorten it that way... but i cant use vlookup afterwards to get other infos ..
because the cell contents are 120 and not 5200120.
so if i have to enter 100 rows with invoice numbers beginning 5200
i will have another 100 rows with 5201, then 5202 and so on .. so everytime i paste the last changing digits in order to type quickly instead 7 or 8 or 9 digits.. thats the reason what i want to accomplish to use vlookup with another list after converting to text string then change back to number as full string..
Must i go through VBA to get this result ? Whats the code?
sorry no idea, thats beyond my limited vba skills
why not do it the other way around
try like this enter your stuff in column b
column c you can copy paste special values elsewhere as required
i thought that way before too and also with concatenate formula.. but it wont work.. too many work
because the numbers arent always 100 times... it can be 48 x or also 56 x .. variable
the 100 times was an example.. and the numbers sorting arent always in order.. first numbers and last digits are variabel .. in different order and its not always 1-100, but it can depending on hour etc... also have missing numbers which arent related to this list.
ie like this
5200152
5200156
5201045
5201048
and so on
i dont even have vba skills..
i cant see how it is easier to keep changing the cell formats in the first place!
I know this response is long after the request, but it is also the first forum I found trying to answer the question I had. I couldn't quickly find the answer, but I did solve it myself.
In response to the original user:
In Cell A7 (in my example) is 20. Rather than formatting at all, in a separate cell, use the RIGHT or LEFT function to insert the 5200 wherever you need it. I did: =RIGHT(A7, LEN(A7) -0)&"5200"
This makes the output of the formula 205200. It is inserting a string text to the right 0 spaces. Then copy the cell and special paste Value only and it will paste the string itself which you will then be able to use the search function or vlookup to find.
With copy and paste, you can copy the formula for an entire column and then copy and value paste that column for the entire set of string cells that you need. If more inserts are needed, just repeat the formula and keep adding columns. Just remember to change the cells the formula is referencing each time otherwise it will only reference the original cell(s) it was set for.
Attachment 833347
Last edited by onetimeanswer; 06-16-2023 at 08:28 AM.
Thanks I will try it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks