• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
291 views

convert a text string into number

Ajit2 asked on
I have two queries:
1.  I have two col. namely folion_no and scheme name. But there are more than one schemes registered under one folio. A scheme name is as long as 25 characters. I want to convert these scheme names which are text into numbers for some purpose. The conversion should be unique. The literally same scheme should be have unique value. If I change a single character of the scheme name, the value should change.

2. I have two sheets. Sheet1 is having col. 1,2,3,4,5,6 and Sheet2 is having 3,2,4,1,6,5,7,8. Actually there are 30 columns. How can I re-arrange the columns of sheet2 as 1,2,3,4,5,6,7,8..... AT ONE GO. In some applications, we canre-arrange the columns where we want to by holding Column Header. Is there any such facility in Excel, 2003 or Excel, 2007?
1 person had this question

Abuse history


The answered status icon Answer
Tom-S replied on

Ajit2,

I doubt you can do the column re-sort 'in one go', unless you want to use a macro. Is it not possible just to copy Sheet1, add anything unique from the exisiting Sheet2 to the copy, delete (or re-name) the existing Sheet2, then re-name the copy as a new Sheet2?

For the unique naming, assuming your data starts in row 2 and Folio No. is in column A, how about using this formula:

=CONCATENATE(A2,ROW()-1)

___________________

Regards, Tom

Be the first person to mark this helpful

Abuse history


The answered status icon Answer
Sheeloo replied on

For 1;

Do you have a list of Folios and unique scheme names? If yes then you can number each scheme within a given folio from 1-n then use VLOOKUP on your details sheet.

If you have folios in Col A and scheme names in Col B then you can use this formula in Col C to genrate unique numbers

=A2&COUNTIF($A$2:A2,A2)

you need to copy it down

Then you can have VLOOKUP like this

(assuming the above list is on Sheet2)

=VLOOKUP(B2,Sheet2!B:C,2,FALSE)

 

If this response answers your question then please mark as Answer. It helps others who browse.
Be the first person to mark this helpful

Abuse history


progress