How to Add a Space Before AM or PM in Excel Time Values
Question details
Insert a space between the time value and AM/PM to correct inconsistent text formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or processing text responses (such as Microsoft Forms data) where times are formatted without spaces, like 12:00PM.
- Observed behavior
- Excel fails to recognize the time values correctly without the space, which causes formulas to break and may display valid times as decimal numbers.
Identify the column containing the inconsistent time values and ensure there is an empty adjacent column to input the correction formula.
Use an IF and SUBSTITUTE Formula
Apply a combined IF, ISNUMBER, SEARCH, and SUBSTITUTE formula to check for missing spaces and automatically add them before AM or PM without modifying already correct data.
This formula checks if a space already exists in the cell. If a space is found, the value remains unchanged. If no space is found, it searches for AM or PM and substitutes it with a space preceding the letters.
Click on an empty cell adjacent to your first inconsistent time value (for example, click B1 if your time data starts in A1).
Type the following formula: =IF(ISNUMBER(SEARCH(" ",A1)),A1,IF(ISNUMBER(SEARCH("AM",A1)),SUBSTITUTE(A1,"AM"," AM"),SUBSTITUTE(A1,"PM"," PM"))) and press Enter.
Click the cell with the formula, grab the small square fill handle at the bottom right corner, and drag it down the column to apply it to all time entries.
Select all the newly corrected cells, right-click, and select 'Format Cells'. Under the 'Number' tab, select 'Custom' and enter 'h:mm AM/PM' to ensure the times do not display as decimal numbers.
Use Flash Fill to Reformat Text
Utilize Excel's Flash Fill feature to recognize the text pattern and automatically insert the space for you without needing complex formulas.
Seamlessly Format Time Data with WPS Spreadsheet
WPS Spreadsheet fully supports Excel formulas like IF, SEARCH, and SUBSTITUTE, making it incredibly easy to fix inconsistent time formats. Enjoy a lightweight, highly compatible alternative for all your data processing needs.
- 1. Open Your Data: Launch WPS Spreadsheet and open the document containing the inconsistent time values.
- 2. Enter the Formula: Select a blank cell adjacent to your data and input the exact same IF and SUBSTITUTE formula used in Excel.
- 3. Format the Cells: Right-click the result cells, click 'Format Cells', choose the 'Time' category, and select your preferred AM/PM format to finalize the display.
Frequently Asked Questions
Why does Excel show my time as a decimal when using this formula?
Excel inherently stores dates and times as serial numbers, with times represented as a fractional part of a 24-hour day (e.g., 12:00 PM is 0.5). If your cell format is set to 'General' or 'Number', the correct time calculation will display as a decimal. You must right-click the cell, select 'Format Cells', and apply a 'Time' format to see it normally.
Will this formula ruin times that already have a space?
No, the formula includes a safeguard. The first segment, =IF(ISNUMBER(SEARCH(" ",A1)),A1,...), specifically checks if a space already exists in the cell. If it detects a space, it leaves the original value exactly as it is without modifying it.
How do I convert the formula results back into raw time values?
Once the formula has added the spaces, highlight the entire column of formula results and press Ctrl+C to copy. Then, right-click the original data column (or the same formula column) and choose 'Paste Values' (the clipboard icon featuring a '123'). This removes the underlying formula while keeping the corrected time text.
How can I prevent this Microsoft Forms formatting issue in the future?
The best way to prevent this is by updating the form source. Add explicit instructions to the Microsoft Forms question description telling users to include a space (e.g., 'Enter time as 12:00 PM'). Alternatively, use the built-in time picker feature if your form platform supports it, which standardizes the data output automatically.
