Search:

Type: Posts; User: AlKey; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.03 seconds.

  1. Replies
    5
    Views
    135

    Re: Formula? Extract text between 2 characters

    Removed post.
  2. Re: Separating City from Address When Only Comma Sits Between City and State

    My formula will skip "Pkwy" and returns "NE Palm Coast" which which is consistent with NE (for "North East")
  3. Re: Separating City from Address When Only Comma Sits Between City and State

    It can be done with a formula

    Assuming address list is in column A

    Enter formula in C1 and copy down

    =IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A1,-LOOKUP(1,-SEARCH($B$1:$B$9&" ",A1)),50),"...
  4. Replies
    4
    Views
    213

    Re: Excel Formula - How to separate full name

    Try this
    Enter formula in cell B1 and drag formula across to C1


    =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",",",2),",",REPT(" ",50)),50*(COLUMNS($A:A)-1)+1,50))
  5. Replies
    11
    Views
    280

    Re: Seperate Cell in Data into 2 seperate cells

    Try this
    Enter formula in B1 and drag formula across

    =TRIM(MID(SUBSTITUTE($A1,"@",REPT(" ",50)),50*(COLUMNS($A:A)-1)+1,50))
  6. Replies
    6
    Views
    309

    Re: Extract data from text string

    Assuming the text string is in cell A1
    1. Enter formula in B1
    =LEFT(A1,18)

    2.Enter in C1
    =LEFT(SUBSTITUTE(A1,B1,""),8)
  7. Replies
    7
    Views
    293

    Re: Repalce letters with actual numbers

    This one will also do the job

    =--SUBSTITUTE(SUBSTITUTE(A1,"B","E9"),"M","E6")
  8. Replies
    7
    Views
    293

    Re: Repalce letters with actual numbers

    Try this
    Enter formula in B1 and copy down

    =-LOOKUP(1,-SUBSTITUTE(A1,{"B","M"},"E"&{9,6}))
  9. Replies
    5
    Views
    266

    Re: Removing Numbers from a cell

    Or this
    <b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
  10. Replies
    4
    Views
    284

    Re: Extract Characters from String

    With data in staring in A1:

    Enter formula in B1

    =IFERROR(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),50,50))/1,"Error")
  11. Replies
    7
    Views
    369

    Re: Excel formula to extract first word

    Enter formula in B1 and copy down

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50))
  12. Replies
    3
    Views
    181

    Re: Basic formula help please...

    Or get rid of ISBLANK function:


    =IF(E8="","CUSTOM TEXT",IF(E8>6,"CUSTOM TEXT",""))
  13. Replies
    4
    Views
    195

    Re: How to sort numbers?

    You did not mention that sorting should be in columns
    Enter formula in B1 and drag across until you see blanks
    For Excel 2010 and higher:

    =IFERROR(AGGREGATE(15,6,--MID(SUBSTITUTE($A1,",",REPT("...
  14. Replies
    4
    Views
    195

    Re: How to sort numbers?

    Assuming the numbers are in cell A1

    If you use Excel version 2010 or higher you can use AGGREGATE function
    Enter formula in cell B1 and drag formula down
    ...
  15. Replies
    2
    Views
    202

    Re: Extract numbers and string after

    Try this
    Assuming data starts in cell A1
    Enter formula in B1 and copy down

    =MID(A1,MATCH(1,INDEX(-MID(A1,COLUMN(1:1),1),0))-4,50)
  16. Replies
    18
    Views
    899

    Re: extract name after :

    Or

    =mid(a1,find(":",a1)+2,1000)
  17. Replies
    18
    Views
    899

    Re: extract name after :

    Or try this

    =REPLACE(A1,1,FIND(":",A1)+1,"")
  18. Replies
    3
    Views
    284

    Re: Converting text to numbers

    Or try these
    To get 3.7
    =LEFT(SUBSTITUTE(A1,"ys ","."),FIND("ms",A1)-3)/1
    To get 3.07
    =SUBSTITUTE(LEFT(A1,FIND("m",A1)-1),"ys ",".0")/1

    <b>Unknown</b><table cellpadding="2.5px" rules="all"...
  19. Thread: Concatenate if

    by AlKey
    Replies
    5
    Views
    325

    Re: Concatenate if

    Or you can use this non-array version

    =TEXTJOIN(", ",1,INDEX(REPT(B$1:H$1,B2:H2="x"),0))
  20. Thread: Concatenate if

    by AlKey
    Replies
    5
    Views
    325

    Re: Concatenate if

    Try this
    Enter array formula in I2 and copy down

    =TEXTJOIN(", ",1,IF(B2:H2="x",B$1:H$1,""))
    **Must be entered with Ctrl+Shift+Enter key combination.
    <b>Unknown</b><table cellpadding="2.5px"...
  21. Replies
    5
    Views
    476

    Re: Coverting 24 to 12-hour with AM/PM Showing

    Try this
    Enter formula in B2 and format as Custom, h:mm:ss and format cell C2 as Custom, h:mm:ss AM/PM using the same formula

    =--SUBSTITUTE(A2,"T"," ")
  22. Replies
    3
    Views
    205

    Re: Extract Number Formula Needed

    A little-bit shorter

    =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),7)
  23. Replies
    3
    Views
    364

    Re: Text to Columns complex issue

    Try this
    Enter formula in cell B1 and drag formula across

    =TRIM(SUBSTITUTE(COLUMNS($A:A)&". "&TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",200)),200*COLUMNS($A:A),200)),COLUMNS($A:A)+1,""))
  24. Re: Need formula for to display overall concatenate value

    Please note that TEXTJOIN function is available only for the Office 365 subscribers.
  25. Replies
    4
    Views
    363

    Re: Extract string with wildcards

    Maybe this
    =TRIM(LEFT(SUBSTITUTE(IF(ISNUMBER(-LEFT(A2)),A2,TRIM(RIGHT(SUBSTITUTE(LEFT(A2,MATCH(1,INDEX(-MID(A2,ROW(A$1:A$99),1),0)))," ",REPT(" ",50)),50)))," ",REPT(" ",50)),50))...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4