0

I have like 1000 of records with cells like below

Jan To Mar
Aug To Dec
Mar, Oct To Dec
Nov
OCT,NOV,DEC

like this I have of 1000 of record with case sensitive letters, i want cells with range to convert as Jan, Feb, Mar like this. can someone help me with this

1

You write i want cells with range to convert as Jan, Feb, Mar

To do that, you can use this VBA function:

Option Explicit
Function expandMonths(S As String) As String
    Dim V As Variant, W As Variant, X As Variant
    Dim dD As Object
    Dim I As Long
    Dim DT1 As Long, DT2 As Long
    Dim arrMonths() As String

Set dD = CreateObject("Scripting.Dictionary")
'Is there a range
V = Split(S, ",")
For Each W In V

        'Is it a range
        If InStr(1, Trim(W), " to ", vbTextCompare) = 0 Then

            'No it is not
            DT1 = Month(DateValue(Trim(W) & " 1, 2000"))
            dD.Add DT1, DT1
        Else

            'Yes, it is a range
            X = Split(Trim(W))
            DT1 = Month(DateValue(X(0) & " 1, 2000"))
            DT2 = Month(DateValue(X(2) & " 1, 2000"))
            For I = DT1 To DT2
                dD.Add I, I
            Next I
        End If
Next W

ReDim arrMonths(1 To dD.Count)
I = 0
For Each V In dD.keys
    I = I + 1

    'Convert month numbers to text
    arrMonths(I) = Format(DateSerial(2000, V, 1), "mmm")
Next V

expandMonths = Join(arrMonths, ", ")

End Function
  • Collect the month numbers into a dictionary
    • create the month numbers by using the Month function against a constructed date
  • The function first splits the string by the comma.
  • If there is a to in any of the splits, then that represents a range
    • Split that by the space and use the first and last entries to construct the range
  • Put the results back together to create the desired string result

enter image description here

  • Thank you so much it's working. – Anusha Tatigyntla Aug 11 at 14:39
  • @AnushaTatigyntla Glad to help. Since my answer seems to be what you require, kindly mark it as Accepted. See What should I do when someone answers my question. – Ron Rosenfeld Aug 11 at 18:53
  • @RonRosenfeld,, it's really great work, but I found a issue if Month value is like May to Jan,, returns #Value error,, expected is May to Jan (next year). – Rajesh S Aug 12 at 5:30
  • @RajeshS Yes. There will be errors if the end month is prior to the start month. There will also be an error if there are overlapping ranges of months to be expanded. There were no examples given of either so it is not clear if this could occur in the data set and, if it did, whether it represents a data entry error, or requires a defined response. Given the examples, I think they would represent a data entry error, so left it as returning an error. If that is not the case, I'm sure the OP will be back. Easily changed if need be. – Ron Rosenfeld Aug 12 at 10:33
-1

To solve the issue you need to follow these steps:

Situation 1:

enter image description here

  • Fill 01/01/2019 in Cell H97 & 03/01/2019 in Cell H98.
  • Write =$H$97 in Cell I97.

N.B. Apply Custom cell format mmm on Cells H97, I97 & H98.

  • Enter this Formula in Cell I98 & better fill down into next 11 rows.

    =TEXT(IF(DATE(YEAR($H$97),MONTH($H$97)+ROW(A1),DAY($H$98))>$H$98,"",DATE(YEAR($I$97),MONTH($I97)+1,DAY($I$97))),"mmm")

N.B.

  • Now, to get set of another months, keep changing Start & End Date values in both H97 & H98.

Situation 2:

enter image description here

Adjust cell references for entire exercises as needed.

  • Thank you. But like that i have some 20000 of records so it will be helpful for me if it can be done through vba – Anusha Tatigyntla Aug 10 at 17:09
  • @AnushaTatigyntla,, glad to help you,, it doesn't matters that how may records you are suppose to handle,, matters is the process,, and you could easily translate whole exercise into VBA ,, but always remember once you need to invest time to design the whole process,, also Edit your post and add that you are in need fo VBA macro,, and add VBA tag to the question too! – Rajesh S Aug 11 at 6:02
  • @AnushaTatigyntla,, better add sample data and the expected output, help us to fix the issue in better way. – Rajesh S Aug 11 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.