Reading through the spaces

[Nanaia]

I have a CSV that is received from another department that I pull into my workbook. One cell contains fractions and the inch mark followed by 17-24 spaces. I'm trying to pull from that cell to indicate what file I need to look in for the information and I'm not sure how the pull the information out of all the extra spaces. I want to pull just the numbers from it (2, 2.5, 3, 3.5, 4 with no inch marks) in a TEXTJOIN. If it were just a single number, I'd use (LEFT(D3,1)) but since the numbers contained in the cell are 1 3/8", 2", 2 1/2", 3", 3 1/2", 4", I can't limit my search to just 1 character. What other way can I do it without modifying the CSV?

 

[DRSteele]

Your question isn't very clear; I tried anyway. In cell A1 I have your data, which contains 20 spaces at the end (where I put an asterix to indicate such). Function SUBSTITUTE can be coerced into replacing the double-quotes by putting either function CHAR(34) or four double-quotes into the old_text argument. Function TRIM will hack out the superfluous spaces. That will report your data without extra spaces and the inch-marks. Is that OK so far?

	A	B	C
1 3/8", 2", 2 1/2", 3", 3 1/2", 4" *

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #E2EFDA"]1 3/8, 2, 2 1/2, 3, 3 1/2, 4 *[/TD]
[TD="bgcolor: #C6E0B4"]1 3/8, 2, 2 1/2, 3, 3 1/2, 4 *[/TD]

</tbody>

Sheet36​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]B1[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(A1,CHAR(34),""))[/TD]
[/TR]
[TR]
[TH="width: 10"]C1[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(A1,"""",""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nanaia]

My apologies for not being more clear. Explaining how this is all linked together is a bit of a challenge for me. I was trying to avoid putting too much information in my question.
Your B1 formula removed all the extra spaces in every scenario I put it through. Thank you. Your assistance is greatly appreciated!