Excel automatically converting 7 digit CAS number to another number date

0 votes

Problem: I am attempting to deal with two lists. In columns A and B, there is one labelled "HYPHEN" and another labelled "CAS Number."

If a hyphen is present in column A, it is placed before the adjacent CAS number, which is then entered below, and so on until all hyphens and CAS numbers are present. Column C utilizes a formula that combines column A and column B and ranks them. To help illustrate this, I've included an image, and the formula to duplicate it is provided below.

CAS number being incorrectly converted to another number when Sorted (highlighted in red)

To replicate the issue: Columns A and B can have any data entered. To replicate the output of column C the formula is given below:

Formula for Column C: =FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B26),",","")&"","//b")

(Formula provided by @Gary's Student on Stack Overflow)

Can someone please help me with this?

Sep 24 in Others by Kithuzzz
• 20,660 points
64 views

1 answer to this question.

0 votes

Looks like you could use:

enter image description here

The formula in D2:

=SUBSTITUTE(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),"'","")

Or:

=MID(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),2,99)
answered Sep 25 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
544 views
0 votes
1 answer

Regular expression to match standard 10 digit phone number

 If you are also interested in matching ...READ MORE

answered Feb 22 in Others by Aditya
• 7,660 points
101 views
0 votes
1 answer

Converting all tabs of excel sheet to PDF

Using VBA, try it like this, for ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
232 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
68 views
0 votes
1 answer

Excel Issue- custom format for Turkish currency

What about: #.##0,00 [$₺-tr-TR] Where #.##0,00 € would do for ...READ MORE

answered Oct 10 in Others by narikkadan
• 37,660 points
140 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
145 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
248 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
77 views
0 votes
1 answer

Java Spring - Writing Excel file and converting to PDF

Since you are using Spring I suggest ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
272 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP