如何使用Excel公式将带前缀的日期字符串转换为指定日期格式？

If your prefixed strings (like AGH20180301) all have a 3-character prefix followed by an 8-digit date in YYYYMMDD format, here are a couple of straightforward formula solutions to get the desired DD/MM/YYYY output:

Solution 1: Concise Formula Using REPLACE & TEXT #

This formula combines string manipulation and date formatting in one step:

=TEXT(--REPLACE(REPLACE(MID(A1,4,8),5,0,"-"),8,0,"-"),"DD/MM/YYYY")

How it works: #

• MID(A1,4,8) extracts the 8-digit date part from the string (skipping the first 3 prefix characters). For AGH20180301, this gives 20180301.
• First REPLACE(...,5,0,"-") inserts a hyphen after the 4th character, turning 20180301 into 2018-0301.
• Second REPLACE(...,8,0,"-") inserts another hyphen after the 7th character, resulting in 2018-03-01 (a valid date string Excel recognizes).
• The double hyphen (--) converts this text date into an Excel date serial number.
• TEXT(..., "DD/MM/YYYY") formats the serial number into your desired DD/MM/YYYY string.

Solution 2: Explicit Date Component Extraction #

If you prefer a more readable formula that breaks down each date part, use this:

=TEXT(DATE(LEFT(MID(A1,4,8),4), MID(MID(A1,4,8),5,2), RIGHT(MID(A1,4,8),2)), "DD/MM/YYYY")

How it works: #

• MID(A1,4,8) again gets the 8-digit date string.
• LEFT(...,4) extracts the year (2018 from 20180301).
• MID(...,5,2) extracts the month (03).
• RIGHT(...,2) extracts the day (01).
• DATE(year, month, day) creates an Excel date value from these components.
• TEXT(..., "DD/MM/YYYY") formats the date into the required string format.

Example Results: #

Note: #

If your prefixes aren't always 3 characters, you can adjust the MID function to start at the first digit instead. For example, using MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),8) to dynamically find the start of the date part.

内容的提问来源于stack exchange，提问作者Mochamad Rinaldy Aulia