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, 2022 in Others by Kithuzzz
• 28,900 points
123 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, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer
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, 2022 in Others by gaurav
• 22,960 points
684 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, 2022 in Others by Aditya
• 7,660 points
219 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, 2022 in Others by narikkadan
• 53,520 points
153 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, 2022 in Others by narikkadan
• 53,520 points
285 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, 2022 in Others by Edureka
• 13,630 points
215 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, 2022 in Others by Edureka
• 13,630 points
392 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, 2022 in Others by Edureka
• 13,630 points
175 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 53,520 points
71 views
0 votes
1 answer

Stop Excel from automatically converting certain text values to dates

I've discovered that getting the desired result ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 53,520 points
52 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