# Search:

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

Page 1 of 6 1

1. ## Thread: Formula? Extract text between 2 characters

by AlKey
Replies
5
Views
127

### Re: Formula? Extract text between 2 characters

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

by AlKey
Replies
12
Views
489

### 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. ## Thread: Separating City from Address When Only Comma Sits Between City and State

by AlKey
Replies
12
Views
489

### 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. ## Thread: Excel Formula - How to separate full name

by AlKey
Replies
4
Views
199

### 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. ## Thread: Seperate Cell in Data into 2 seperate cells

by AlKey
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. ## Thread: Extract data from text string

by AlKey
Replies
6
Views
297

### 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. ## Thread: Repalce letters with actual numbers

by AlKey
Replies
7
Views
278

### Re: Repalce letters with actual numbers

This one will also do the job

=--SUBSTITUTE(SUBSTITUTE(A1,"B","E9"),"M","E6")
8. ## Thread: Repalce letters with actual numbers

by AlKey
Replies
7
Views
278

### 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. ## Thread: Removing Numbers from a cell

by AlKey
Replies
5
Views
250

### 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. ## Thread: Extract Characters from String

by AlKey
Replies
4
Views
272

### 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. ## Thread: Excel formula to extract first word

by AlKey
Replies
7
Views
345

### Re: Excel formula to extract first word

Enter formula in B1 and copy down

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50))

by AlKey
Replies
3
Views
177

### Re: Basic formula help please...

Or get rid of ISBLANK function:

=IF(E8="","CUSTOM TEXT",IF(E8>6,"CUSTOM TEXT",""))
13. ## Thread: How to sort numbers?

by AlKey
Replies
4
Views
190

### 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. ## Thread: How to sort numbers?

by AlKey
Replies
4
Views
190

### 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. ## Thread: Extract numbers and string after

by AlKey
Replies
2
Views
195

### 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. ## Thread: extract name after :

by AlKey
Replies
18
Views
875

### Re: extract name after :

Or

=mid(a1,find(":",a1)+2,1000)
17. ## Thread: extract name after :

by AlKey
Replies
18
Views
875

### Re: extract name after :

Or try this

=REPLACE(A1,1,FIND(":",A1)+1,"")
18. ## Thread: Converting text to numbers

by AlKey
Replies
3
Views
276

### 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

by AlKey
Replies
5
Views
316

### Re: Concatenate if

Or you can use this non-array version

=TEXTJOIN(", ",1,INDEX(REPT(B\$1:H\$1,B2:H2="x"),0))

by AlKey
Replies
5
Views
316

### 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.
21. ## Thread: Coverting 24 to 12-hour with AM/PM Showing

by AlKey
Replies
5
Views
439

### 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. ## Thread: Extract Number Formula Needed

by AlKey
Replies
3
Views
201

### 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. ## Thread: Text to Columns complex issue

by AlKey
Replies
3
Views
342

### 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. ## Thread: Need formula for to display overall concatenate value

by AlKey
Replies
11
Views
432

### Re: Need formula for to display overall concatenate value

Please note that TEXTJOIN function is available only for the Office 365 subscribers.
25. ## Thread: Extract string with wildcards

by AlKey
Replies
4
Views
361

### 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