Home

Number and Text in same cell but need the cell to read as a number

%3CLINGO-SUB%20id%3D%22lingo-sub-1054308%22%20slang%3D%22en-US%22%3ENumber%20and%20Text%20in%20same%20cell%20but%20need%20the%20cell%20to%20read%20as%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054308%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20a%20cell%20be%20%22read%22%20as%20a%20number%20for%20formuals%20but%20have%20text%20after%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eie.%201%2C234%20sf%20but%20need%20to%20use%20the%20number%20for%20a%20formula%20in%20another%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1054308%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054319%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20and%20Text%20in%20same%20cell%20but%20need%20the%20cell%20to%20read%20as%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054319%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481270%22%20target%3D%22_blank%22%3E%40nicole5462%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20how%20number%20%26amp%3B%20text%20appeared%20in%20first%20cell.%20For%20example%2C%20you%20may%20apply%20custom%20format%20to%20the%20cell%20with%201234%20as%26nbsp%3B%23%2C%23%23%23%22%20sf%22%20and%20it%20will%20be%20shown%20as%201%2C234%20sf.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20there%20is%20some%20logic%20in%20how%20and%20text%20placed%20(e.g.%20number%20is%20before%20first%20space)%20it%20could%20be%20extracted%20by%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20no%20logic%20-%20perhaps%20it's%20possible%20to%20extract%20digits%20one%20by%20one%2C%20but%20it'll%20be%20bit%20complicated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054766%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20and%20Text%20in%20same%20cell%20but%20need%20the%20cell%20to%20read%20as%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481270%22%20target%3D%22_blank%22%3E%40nicole5462%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20To%20add%20to%20what%20Sergei%20already%20said%2C%20the%20best%20long%20term%20solution%20to%20your%20problem%20is%20to%20re-design%20your%20spreadsheet%20so%20that%20you%20don't%20have%20the%20problem%20in%20the%20first%20place.%20Unless%2C%20as%20he%20said%2C%20there's%20a%20known%20and%20very%20regular%20pattern%20(e.g.%2C%20digits%20are%20always%20limited%20to%20four%2C%20text%20to%20two%20characters)...%20unless%20there's%20some%20regular%20pattern%20like%20that%2C%20you%20will%20always%20be%20faced%20with%20the%20challenge%20of%20parsing%20out%20what%20should%20have%20been%20two%20separate%20fields%20or%20cells%20in%20the%20first%20place.%20It's%20one%20thing%20to%20have%20a%20part%20number%20that%20combines%20arabic%20numerals%20with%20text%3B%20quite%20another%20to%20have%20a%20cell%20with%2C%20say%2C%20numbers%20ordered%20and%20city%20abbreviation%20(if%20that's%20what%20%22sf%22%20is%20in%20your%20example)....%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
nicole5462
Occasional Visitor

How can a cell be "read" as a number for formuals but have text after it.

 

ie. 1,234 sf but need to use the number for a formula in another cell

2 Replies

@nicole5462 

It depends on how number & text appeared in first cell. For example, you may apply custom format to the cell with 1234 as #,###" sf" and it will be shown as 1,234 sf.

 

If there is some logic in how and text placed (e.g. number is before first space) it could be extracted by formula.

 

If no logic - perhaps it's possible to extract digits one by one, but it'll be bit complicated. 

@nicole5462   To add to what Sergei already said, the best long term solution to your problem is to re-design your spreadsheet so that you don't have the problem in the first place. Unless, as he said, there's a known and very regular pattern (e.g., digits are always limited to four, text to two characters)... unless there's some regular pattern like that, you will always be faced with the challenge of parsing out what should have been two separate fields or cells in the first place. It's one thing to have a part number that combines arabic numerals with text; quite another to have a cell with, say, numbers ordered and city abbreviation (if that's what "sf" is in your example)....