Remove partial data from cells

[palmer41420]

Using Excel 2013:

I have a spreadsheet with values in this format. ="10/26"

This is not a calculated value. That is how it extracted from another database. I have over 100,000 values in this format so doing them individually not realistic.

I need a formula or process to extract only the 10. I would like to leave the original column as is and have a column next to it with the new value of 10 in this example.

I previously used Text to Columns but the original value was only 10/26. I can't get Text to Columns to work since there are = and ".

I found several posts using LEFT and SUBSTITUTE functions but could not figure out how to make it work for my situation.

Thanks in advance for your assistance.

 

[Caribeiro77]

Will the values always have that format? If so, and assuming your data starts in A1, in B1 try:

=MID(TRIM(A1),3,2)

 

[palmer41420]

Will the values always have that format? If so, and assuming your data starts in A1, in B1 try:

=MID(TRIM(A1),3,2)

Thank you for your quick response.

I should have mentioned that the values will range from =" 0/26" to =" 26/26" so that formula will not work for all of my entries. This range is showing the number correct out of the number possible.

Also, I didn't realize there was a space between " and the first number. I adjusted your formula once I noticed my mistake to make it work for most of my entries.

Any other ideas since counting from the left will not work?

 

[István Hirsch]

Select the column, Replace "=_ (quote equals space) with nothing then use Text to Columns as you used to.

If there are more spaces, Replace *_ (asterisk space) with nothing instead.

Copy the original column into another column before processing.

 

[palmer41420]

Select the column, Replace "=_ (quote equals space) with nothing then use Text to Columns as you used to.

If there are more spaces, Replace *_ (asterisk space) with nothing instead.

Copy the original column into another column before processing.

When I try that, it says it can't find any data to replace. The data displays as 10/26 in the column but when I click the cell it shows =" 10/26" for the value in the formula bar.

 

[István Hirsch]

Then try this:

Copy the column to process (say, column A), select the next column (B), and paste the copied column with PasteSpecial as Value in column B. Then with column B still selected, Replace /* with nothing. Hope this works now.

 

[palmer41420]

Thanks for the suggestions. They all helped me figure it out. I used the suggestion of copying the column and pasting values in a neighboring column. I then used text to columns to separate the data using / as the delimiter.