I need to add a space in between text and numbers. For example, I want to change MS1-27
to MS 1-27
while keeping the data in one cell. How can I do this in Excel?
-
Is the text always the first two characters or does the position where the space is needed vary?– fixer1234Jan 19, 2015 at 23:31
2 Answers
I'm not sure what you mean by "keeping the data in one cell".
A formula requires putting the formula in a different cell. For example:
=TRIM(REPLACE(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),0," "))
will place a space
before the first digit. The TRIM wrapper is in case there already is a space
.
If you need to have it happen within the cell, you will need a VBA Macro as a formula cannot alter the contents of a different cell.
This might be a job for regex. MS Excel allows use of VBA for search-and-replace using regular expressions, so you could specify "replace the first occurrence in a cell of a character from 0-9 with a space and that character". To enable regex, see http://www.pither.com/simon/blog/2010/01/31/regex-search-and-replace-in-excel.
regex is a rather complex subject, and there is help for MS Word at https://support.office.com/en-ca/article/Find-and-replace-text-by-using-regular-expressions-Advanced-eeaa03b0-e9f3-4921-b1e8-85b0ad1c427f. You'll need to specify more about the details of the search (as fixer1234 asks, is it simply adding a space after the second character in each cell, or is the text of variable length?). See also https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops and https://www.udemy.com/blog/vba-regex/.