Format text string with number values

[ChristineJ]

The following text string is in cell A1: 700, 7000, 9730

The text string in cell A1 is used in another text string in cell A3: ="The numbers "&A1&" appear in cell A1."

The result in A3 is: The numbers 700, 7000, 9730 appear in cell A1.

Is there a function/formula that would (1) remove the comma before the second-to-last number, (2) add comma separators to numbers over 999, and add "and" before the last number, as follows: The numbers 700, 7,000 and 9,730 appear in cell A1.

Thanks! CJ

 

[RoryA]

I'm not convinced using commas in numbers in a comma separated list is particularly clear, but you could do something like this:

=LET(nums,TEXT(TEXTSPLIT(A1,","),"#,##0"),"The numbers "&TEXTJOIN(", ",,DROP(nums,,-1))&" and "&TAKE(nums,,-1)&" appear in cell A1.")

 

[ChristineJ]

Wow - awesome.

I agree with you about the issue of using commas in numbers in a comma separated list. I've been staring at it to see if it is clear to read. But I wanted to know how to do it - assuming I go that route.

Thanks for the quick solution! I never would have known how to do that! Learned something. CJ

 

[HighAndWilder]

The following text string is in cell A1: 700, 7000, 9730

The text string in cell A1 is used in another text string in cell A3: ="The numbers "&A1&" appear in cell A1."

The result in A3 is: The numbers 700, 7000, 9730 appear in cell A1.

Is there a function/formula that would (1) remove the comma before the second-to-last number, (2) add comma separators to numbers over 999, and add "and" before the last number, as follows: The numbers 700, 7,000 and 9,730 appear in cell A1.

Thanks! CJ

A slightly longer formula.

I'll learn from the one that RoryA submitted.

="The numbers "& SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",TRUE,TEXT(TEXTSPLIT($A$1,","),"#,##0"))," ",", ",1)," "," and ",2) &" appear in cell A1."

 

[Micron]

In case anyone ever stumbles on this thread and needs a vba solution I came up with this (it would be a function call in the desired cells)

Public Function BuildString(str As String) As String
Dim ary
Dim i As Integer, ubnd As Integer
Dim strOut As String

ary = Split(str, ", ")
ubnd = UBound(ary)
For i = 0 To ubnd
    ary(i) = Format(ary(i), "#,###")
Next

For i = 0 To ubnd - 1
    strOut = strOut & ary(i) & ", "
Next
strOut = Left(strOut, InStrRev(strOut, ",") - 1)
strOut = "The numbers " & strOut & " and " & ary(ubnd) & " appear in cell A1"
BuildString = strOut

End Function

To make it versatile would require modifying to grab the cell address and use that instead of "A1". Then you'd be able to drag it down a sheet for every row as required. I didn't do that because only one row seemed applicable in the OP.