INDEX/MATCH with Return Numbers Only

Morey

New Member
Joined
Sep 24, 2018
Messages
37
The following INDEX/MATCH formula works but, I would like the return to be just the numbers (no text). Help modifying formula would be appreciated.

Code:
 =IFERROR(INDEX('SDC TRIRIGA'!$C$1:$C$15000,MATCH(A2,'SDC TRIRIGA'!$A$1:$A$15000,0)),"")

Current Return:
REQ-1187706

<tbody>
</tbody>
Would like this Return:
1187706

<tbody>
</tbody>
Regards,

Morey
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Will all the returns end in a 7 digit number?
 
Upvote 0
In that case try
=IFERROR(RIGHT(INDEX('SDC TRIRIGA'!$C$1:$C$15000,MATCH(A2,'SDC TRIRIGA'!$A$1:$A$15000,0)),7)*1,"")
This will convert the text into a number. If you want to keep it as text, delete the part in red
 
Upvote 0
In that case try
=IFERROR(RIGHT(INDEX('SDC TRIRIGA'!$C$1:$C$15000,MATCH(A2,'SDC TRIRIGA'!$A$1:$A$15000,0)),7)*1,"")
This will convert the text into a number. If you want to keep it as text, delete the part in red

Fluff,

That did it. Noticed, that the data has a space after the last digit (1187706 <----space found here). To account for this ",7)*1" was edited to ",8)*1". Any way to remove this excessive space after the last digit?

Thanks,

Morey
 
Upvote 0
The *1 should get rid of the space (assuming it's a normal space)
 
Upvote 0
The *1 should get rid of the space (assuming it's a normal space)

Fluff,

After further investigation, you are correct the final return value is indeed space less. Thanks for all the help.



Regards,

Morey
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top