Formatting a return from a formula to a number

[gr8trthanu72]

Hello - not having a good time with this problem.

In column "D" I have a list of order numbers which depending on the first three characters determine the type of order.
IE:060 is regular order / 070 is regular order / 800 is rush order.

I have a table the holds all the order prefixes (060/070/800) and the type of order.

I have a MID formula in column R to pull the first three characters but when I use vlookup in column T the result is #NA because the search criteria is not the same format as the table I am searching for.

However, if I copy the entire R row and paste into column S and then use the "convert to number" that appears when there is a format issue I get the expected result in column U.

How can I automate the formatting of column R in a number format with a macro?

worksheet snipit below.

Thank You

D	R	S	T	U
License Plate Number	Order Type W/O Format	Order Type W Format	Without Format	With Format
060192094-A1	060	60​	#N/A​	Regular_Orders
060192169-A2	060	60​	#N/A​	Regular_Orders
060192191-A2	060	60​	#N/A​	Regular_Orders
060192210-A1	060	60​	#N/A​	Regular_Orders
060192216-A1	060	60​	#N/A​	Regular_Orders
060192222-A1	060	60​	#N/A​	Regular_Orders
060192240-A1	060	60​	#N/A​	Regular_Orders
060192251-A1	060	60​	#N/A​	Regular_Orders
060192270-A1	060	60​	#N/A​	Regular_Orders
060192274-A1	060	60​	#N/A​	Regular_Orders
060192275-A2	060	60​	#N/A​	Regular_Orders
060192277-A1	060	60​	#N/A​	Regular_Orders

 

[sandy666]

try =--MID(... etc

 

[gr8trthanu72]

I struggled with this for along time for such an easy fix

Dont even know what --Mid( does but will research but it worked

Thank YOu

 

[sandy666]

double unary will change text number to number number
--MID
1*MID
0+MID
all doing the same
result cell should be in number format

 

[gr8trthanu72]

thank you - perhaps you could help with one other issue.

I have a main sheet with thousands of rows of data.

In column S there is either "Rush", "Regular" or "Dealer" in each row.

I have three sheets that are named after above (IE: Rush)

If a row has "Rush" in column S, I would like excel to copy columns A:S and paste in the sheet entitled "Rush" starting in row 2

This would be the same for "Regular" and "Dealer"

Again thank you

 

[sandy666]

this is a different question, post it in new thread