Question
286 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