SOLVED

Minimum and Maximum Difficulty

%3CLINGO-SUB%20id%3D%22lingo-sub-1951143%22%20slang%3D%22en-US%22%3EMinimum%20and%20Maximum%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951143%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20struggling%20to%20solve%20a%20formula%20for%20this%20assignment.%20I%20have%20a%20number%20in%20ranges%20in%20one%20cell%2C%20I%20need%20to%20somehow%20split%20the%20cell%20numbers%20into%20two%20different%20columns.%20The%20first%20number%20in%20the%20cell%20is%20the%20minimum%2C%20and%20the%20second%20number%20in%20the%20cell%20is%20the%20maximum.%20I%20have%20attached%20an%20image%20for%20reference.%20For%20example%2C%20in%20M8%2C%20I%20need%20to%20somehow%20format%20the%208700%20minimum%20(small%20number)%20into%20N8%208900%20Maximum%20(large%20number)%20into%20O8.%20I%20need%20to%20do%20this%20for%20each%20row%20in%20Column%20M%20that%20has%20the%20number%20ranges%20in%20each%20cell.%20Column%20N%20is%20for%20minimum%20number%20(small%20number)%20and%20Column%20O%20is%20for%20maximum%20number%20(large%20number).%20Can%20you%20please%20help%20me%20create%20a%20formula%20for%20Columns%20N%20and%20O%20in%20order%20to%20fulfill%20this%20assignment%20without%20taking%20years%20to%20complete%20manually%3F%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1951143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951335%22%20slang%3D%22en-US%22%3ERe%3A%20Minimum%20and%20Maximum%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951335%22%20slang%3D%22en-US%22%3Euse%20text%20to%20column%20with%20separation%20(-)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951398%22%20slang%3D%22en-US%22%3ERe%3A%20Minimum%20and%20Maximum%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F889333%22%20target%3D%22_blank%22%3E%40anniehoang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20is%20a%20separation%20which%20is%20(%20-%20)%20in%20almost%20all%20the%20rows%2C%20except%20the%20second%20and%20third%26nbsp%3B%20rows.%3CBR%20%2F%3EWould%20please%20follow%20the%20pattern%20and%20use%20(-)%20separation%20then%2C%20you%20can%20use%20this%20formula%20assuming%20that%20your%20data%20on%20A1%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(A1%2CFIND(%22-%22%2CA1)-1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951597%22%20slang%3D%22en-US%22%3ERe%3A%20Minimum%20and%20Maximum%20Difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951597%22%20slang%3D%22en-US%22%3Eplease%20share%20file%20and%20please%20fill%20required%20result%20in%20first%20row%20for%20sample.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I have been struggling to solve a formula for this assignment. I have a number in ranges in one cell, I need to somehow split the cell numbers into two different columns. The first number in the cell is the minimum, and the second number in the cell is the maximum. I have attached an image for reference. For example, in M8, I need to somehow format the 8700 minimum (small number) into N8 8900 Maximum (large number) into O8. I need to do this for each row in Column M that has the number ranges in each cell. Column N is for minimum number (small number) and Column O is for maximum number (large number). Can you please help me create a formula for Columns N and O in order to fulfill this assignment without taking years to complete manually? Thank you!

4 Replies
best response confirmed by anniehoang (Occasional Contributor)
Solution
use text to column with separation (-)

@anniehoang 

 

there is a separation which is ( - ) in almost all the rows, except the second and third  rows.
Would please follow the pattern and use (-) separation then, you can use this formula assuming that your data on A1: 

=LEFT(A1,FIND("-",A1)-1)

please share file and please fill required result in first row for sample.

@devyadav2008 This worked! Thank you!