[Solved] HOW TO SEPARATE TEXTS USING EXCEL
- Thread starter YAWFRANK
- Start date
Hello YAWFRANK,
Welcome to our ExcelDemy Forum! Based on your post, I understand you wish to separate the text E057707070033 into three different cells in Excel. Assuming you have static text lengths, one easy way to do this is to merge the MID and CHOOSE functions. The formula is as follows:
Code:
=MID($A2,CHOOSE({1,2,3},1,4,LEN($A2)-3),{3,6,4})
The formula is a dynamic array formula in Excel that can be used to split a text string into three parts and extract different lengths of characters from the text string.
Note:
- Instead of pressing just the Enter key, you need to press the combination of keys: Ctrl + Shift + Enter.
- This formula cannot handle inputs that have different lengths.
Regards,
Yousuf Shovon
Hello Yawfrank,
I appreciate your concerns and sincerely apologize for the misunderstanding. Although the previous formula is displaying the whole numbers ( Prefix, Mid information, and Suffix) in my Excel 365 version, it should also work for other versions as well if you follow the instructions correctly. Further, I am attaching my Excel file here for a better understanding. However, if you still face the same issue, you can apply another method as described below.
In this new method, we will manually apply LEFT, MID, and RIGHT functions to divide the numbers individually and put them in 3 separate cells.
- To obtain the prefix, enter the below formula and press Enter key.
Code:
=LEFT(A2,3)- For mid information,
Code:
=MID(A2,4,6)- To get the suffix, type:
Code:
=RIGHT(A2,4)- Thus, you obtain the separated texts.
Please let me know if you still have further issues with this.
Best Regards,
Yousuf Shovon
Dear Frank,
Did you consider using the VBA function instead of the DATEDIF function? I have solved the issue using this method and it works.
Here is the VBA code for this.
Here, I have created 3 VBA functions. Copy the code and paste it into a module. Afterward, in D5, use the VBA function created.
=diff(B5,C5)
Thus, you obtained the desired result. I have attached the Excel file here. Follow the article https://www.exceldemy.com/how-to-subtract-dates-in-excel-to-get-years/ for other methods.
Regards,
Yousuf Shovon
Did you consider using the VBA function instead of the DATEDIF function? I have solved the issue using this method and it works.
Here is the VBA code for this.
Code:
Option Explicit
Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = False) As Variant
On Error GoTo Err_Diff2Dates
Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booCalcWeeks As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim lngDiffWeeks As Long
Dim varTemp As Variant
Const INTERVALS As String = "dmyhnsw"
'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter
'Check that valid dates have been entered
If IsNull(Date1) Then Exit Function
If IsNull(Date2) Then Exit Function
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If
Diff2Dates = Null
varTemp = Null
'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)
booCalcWeeks = (InStr(1, Interval, "w") > 0)
'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If
If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If
If booCalcWeeks Then
lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("ww", lngDiffWeeks, Date1)
End If
If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If
If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If
If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If
If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If
If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If
If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If
If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
If booCalcWeeks Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
End If
End If
If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If
If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If
If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If
If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If
If booSwapped Then
varTemp = "-" & varTemp
End If
Diff2Dates = Trim$(varTemp)
End_Diff2Dates:
Exit Function
Err_Diff2Dates:
Resume End_Diff2Dates
End Function
Public Function HrsMinDiff(ByVal d1 As Variant, ByVal d2 As Variant) As String
Dim s As String, var
s = Diff2Dates("hn", d1, d2, True)
s = Replace$(Replace(s, " hours ", ":"), " hour ", ":")
s = Replace$(Replace(s, " minutes", ""), " minute", "")
var = Split(s, ":")
HrsMinDiff = Format$(var(0), "00") & ":" & Format$(var(1), "00")
End Function
Public Function diff(ByVal d1 As Variant, d2 As Variant) As String
Dim ret As String
d1 = CDate(d1): d2 = CDate(d2)
ret = Diff2Dates("ymd", d1, d2, True)
If Left$(ret, 1) = "-" Then ret = Mid$(ret, 2)
diff = ret
End FunctionHere, I have created 3 VBA functions. Copy the code and paste it into a module. Afterward, in D5, use the VBA function created.
=diff(B5,C5)
Thus, you obtained the desired result. I have attached the Excel file here. Follow the article https://www.exceldemy.com/how-to-subtract-dates-in-excel-to-get-years/ for other methods.
Regards,
Yousuf Shovon
I am glad it was helpful for you, Frank.
Hi Yousuf,
Can I ask for your help with a date \ hours problem? I would like to solve this is excel without using VBA.
I want to book my time based on tasks, but then also include delays.
Task A = 9 hours (1 Day and 1 hour)
Task B = 15 hours (1 day and 7 hours)
Task C = 5 hours.
Total Hours = 29 hours (3 days 5 hours.) - This I can calculate... except when there are 11 hours I don't know how to convert the next 8 into another day plus 3 hours.
Then...
If one day I need to add a 1 hour meeting - Project planning
each subsequent day a 30 minutes of Stand up meeting.
Each day allocate 15 minutes for admin.
3 days, 5 hours now looks like
3d5h + 1h + 3x30m + 3x15m =
3d5h + 1h + 1.5h + 45m
3d5h + 3h 15m
3d8h15m which is...
4d0h15m + 30m + 15 (add the additional hours for the additional new day.) This smacks of circular formulas...
4d1h
I hope this all makes sense. I'm sure you have had this question before.
Please help.
Thank you.
Gary
Can I ask for your help with a date \ hours problem? I would like to solve this is excel without using VBA.
I want to book my time based on tasks, but then also include delays.
Task A = 9 hours (1 Day and 1 hour)
Task B = 15 hours (1 day and 7 hours)
Task C = 5 hours.
Total Hours = 29 hours (3 days 5 hours.) - This I can calculate... except when there are 11 hours I don't know how to convert the next 8 into another day plus 3 hours.
Then...
If one day I need to add a 1 hour meeting - Project planning
each subsequent day a 30 minutes of Stand up meeting.
Each day allocate 15 minutes for admin.
3 days, 5 hours now looks like
3d5h + 1h + 3x30m + 3x15m =
3d5h + 1h + 1.5h + 45m
3d5h + 3h 15m
3d8h15m which is...
4d0h15m + 30m + 15 (add the additional hours for the additional new day.) This smacks of circular formulas...
4d1h
I hope this all makes sense. I'm sure you have had this question before.
Please help.
Thank you.
Gary
Hello Gary,
Welcome to our ExcelDemy Forum! I understand you wish to compose an Excel formula. Thanks for the detailed explanation although a demo dataset would be really helpful. Further, I have questions like,
What are we taking as input?
Is it a string saying '9 hours (1 Day and 1 hour)' or two separate columns for hours - i.e. 9, 23, or something else?
Or perhaps a start time and task time?
However, I guess simple adding be sufficient in your case. You can use the below formula:
=[Startdate]+Hour/24
Also, to convert the total hours to days and hours, you can use the INT and MOD functions.
=INT(Hour/24)&" days, "&MOD(Hour,24)&" hours"
I have attached an Excel file to assist u. Can you show me an example if you still face issues regarding this?
Regards,
Yousuf Shovon