| Author |
Message |
 |
|
|
|
|
Advert
|
Forum adverts like this one are shown to any user who is not logged in. Join us by filling out a tiny 3 field form and you will get your own, free, dakka user account which gives a good range of benefits to you:
- No adverts like this in the forums anymore.
- Times and dates in your local timezone.
- Full tracking of what you have read so you can skip to your first unread post, easily see what has changed since you last logged in, and easily see what is new at a glance.
- Email notifications for threads you want to watch closely.
- Being a part of the oldest wargaming community on the net.
If you are already a member then feel free to login now. |
|
 |
2016/08/30 18:38:45
Subject: Microsoft Excel function help
|
|
Regular Dakkanaut
|
I have a question about Microsoft excel. I have a column of whole numbers ranging from 1 to 99999. I need to convert the numbers into 5 digit numbers and add FYS to the beginning. for example 1 becomes FYS00001. I can format the number column into a 5 digit number and I can use the concatenate function to add an FYS from another column to it but when I do it reverts back to a single digit. so when I concatenate FYS and 00001 it becomes FYS1. any help on this would be most appreciated it. Thank you.
|
|
|
 |
|
2016/08/30 18:45:54
Subject: Microsoft Excel function help
|
|
Longtime Dakkanaut
|
Crazy Jay wrote:I have a question about Microsoft excel. I have a column of whole numbers ranging from 1 to 99999. I need to convert the numbers into 5 digit numbers and add FYS to the beginning. for example 1 becomes FYS00001. I can format the number column into a 5 digit number and I can use the concatenate function to add an FYS from another column to it but when I do it reverts back to a single digit. so when I concatenate FYS and 00001 it becomes FYS1. any help on this would be most appreciated it. Thank you.
Can you not just convert your 00001 to 'text' and then concatenate them?
|
"Because while the truncheon may be used in lieu of conversation, words will always retain their power. Words offer the means to meaning, and for those who will listen, the enunciation of truth. And the truth is, there is something terribly wrong with this country, isn't there? Cruelty and injustice, intolerance and oppression. And where once you had the freedom to object, to think and speak as you saw fit, you now have censors and systems of surveillance coercing your conformity and soliciting your submission. How did this happen? Who's to blame? Well certainly there are those more responsible than others, and they will be held accountable, but again truth be told, if you're looking for the guilty, you need only look into a mirror. " - V
I've just supported the Permanent European Union Citizenship initiative. Please do the same and spread the word!
"It's not a problem if you don't look up." - Dakka's approach to politics |
|
|
|
|
2016/08/30 18:46:02
Subject: Microsoft Excel function help
|
|
Decrepit Dakkanaut
|
How did you get the numbers to format to 00001? Which format did you use?
|
DA:70S+G+M+B++I++Pw40k08+D++A++/fWD-R+T(M)DM+
 |
|
|
|
|
2016/08/30 18:51:32
Subject: Microsoft Excel function help
|
|
Multispectral Nisse
Luton, UK
|
Unless you really need to use existing data for some reason, isn't the easiest way just to type FYS00001 in row and then drag it down? I've just done it on my Excel and it took about 40 seconds to drag down to 99999. Not exactly formula fast, I grant you.
|
“Good people are quick to help others in need, without hesitation or requiring proof the need is genuine. The wicked will believe they are fighting for good, but when others are in need they’ll be reluctant to help, withholding compassion until they see proof of that need. And yet Evil is quick to condemn, vilify and attack. For Evil, proof isn’t needed to bring harm, only hatred and a belief in the cause.” |
|
|
|
|
2016/08/30 19:02:26
Subject: Microsoft Excel function help
|
|
Decrepit Dakkanaut
|
 Riquende wrote:Unless you really need to use existing data for some reason, isn't the easiest way just to type FYS00001 in row and then drag it down? I've just done it on my Excel and it took about 40 seconds to drag down to 99999. Not exactly formula fast, I grant you.
Boom. Done!
|
DA:70S+G+M+B++I++Pw40k08+D++A++/fWD-R+T(M)DM+
|
|
|
|
|
2016/08/30 19:02:29
Subject: Microsoft Excel function help
|
|
Longtime Dakkanaut
|
 kronk wrote:How did you get the numbers to format to 00001? Which format did you use?
Ah sorry I read you had that bit already.
OK so go to format cells and under the number tab go to "Custom".
On the right had side there underneath the word "Type:" enter in the number of zeros you want. You should then see what you are going to get under the title "Sample" just above.
|
"Because while the truncheon may be used in lieu of conversation, words will always retain their power. Words offer the means to meaning, and for those who will listen, the enunciation of truth. And the truth is, there is something terribly wrong with this country, isn't there? Cruelty and injustice, intolerance and oppression. And where once you had the freedom to object, to think and speak as you saw fit, you now have censors and systems of surveillance coercing your conformity and soliciting your submission. How did this happen? Who's to blame? Well certainly there are those more responsible than others, and they will be held accountable, but again truth be told, if you're looking for the guilty, you need only look into a mirror. " - V
I've just supported the Permanent European Union Citizenship initiative. Please do the same and spread the word!
"It's not a problem if you don't look up." - Dakka's approach to politics |
|
|
|
|
2016/08/30 19:13:19
Subject: Microsoft Excel function help
|
|
Decrepit Dakkanaut
|
I see.
To keep it as a number, go into Custom and type "FYS"00000
|
DA:70S+G+M+B++I++Pw40k08+D++A++/fWD-R+T(M)DM+
|
|
|
|
|
2016/08/30 19:20:14
Subject: Microsoft Excel function help
|
|
Longtime Dakkanaut
|
kronk wrote:I see.
To keep it as a number, go into Custom and type "FYS"00000
Yes exactly, but in this case I'm assuming that the OP wanted to manage the numbers separately which makes it a bit more awkward from what I can see - but I have not played around with all the custom options to be fair.
|
"Because while the truncheon may be used in lieu of conversation, words will always retain their power. Words offer the means to meaning, and for those who will listen, the enunciation of truth. And the truth is, there is something terribly wrong with this country, isn't there? Cruelty and injustice, intolerance and oppression. And where once you had the freedom to object, to think and speak as you saw fit, you now have censors and systems of surveillance coercing your conformity and soliciting your submission. How did this happen? Who's to blame? Well certainly there are those more responsible than others, and they will be held accountable, but again truth be told, if you're looking for the guilty, you need only look into a mirror. " - V
I've just supported the Permanent European Union Citizenship initiative. Please do the same and spread the word!
"It's not a problem if you don't look up." - Dakka's approach to politics |
|
|
|
|
2016/08/30 19:27:47
Subject: Microsoft Excel function help
|
|
Regular Dakkanaut
|
 Whirlwind wrote: kronk wrote:I see.
To keep it as a number, go into Custom and type "FYS"00000
Yes exactly, but in this case I'm assuming that the OP wanted to manage the numbers separately which makes it a bit more awkward from what I can see - but I have not played around with all the custom options to be fair.
this actually solved my problem really well, thank you. I was originally using a custom format to get 5 digits. I didn't know how to add the letters in. Thank you again.
|
|
|
|
|
2016/08/31 03:10:45
Subject: Re:Microsoft Excel function help
|
|
The Dread Evil Lord Varlak
|
I have learned something reading through this. I would have produced a nested formula with a len function telling it how many 0 to add before the number. All very fiddly. Instead I've just found out how powerful the custom number setting can be.
Thanks everyone.
|
“We may observe that the government in a civilized country is much more expensive than in a barbarous one; and when we say that one government is more expensive than another, it is the same as if we said that that one country is farther advanced in improvement than another. To say that the government is expensive and the people not oppressed is to say that the people are rich.”
Adam Smith, who must have been some kind of leftie or something. |
|
|
|
|
|
|
